将表联接到子查询 [英] Join table to a subquery

问题描述

在以前的帖子中有人帮助我子查询.现在，我想添加到查询中，但收到一条错误消息. (我仍在学习有关子查询的规则.)

In a previous post someone helped me with a subquery. Now I'd like to add to the query but am getting an error message. (I'm still learning rules around subqueries.)

下面更新了SQL.错误是:

Updated SQL below. The error is:

[Amazon](500310) Invalid operation: invalid reference to FROM-clause entry for table "application_stages";

SELECT t1.*, applications.status, applications.stage_name
FROM application_stages t1
JOIN (
    select application_id, max(exited_on) as exited_on
    from application_stages 
    group by application_id
) t2 
USING (application_id,exited_on)
join applications on application_stages.application_id = applications.id
where application_id in ('91649746', '91991364', '96444221')

推荐答案

分配别名时，需要在所有子句中一致使用它们，并避免在其他表中使用相同名称的列.考虑进行以下调整

When you assign aliases, you need to consistently use them in all clauses and to avoid ambiguity of same named columns in other tables. Consider following adjustment

SELECT s.*, a.status, a.stage_name
FROM application_stages s
JOIN (
    select application_id, max(exited_on) as exited_on
    from application_stages 
    group by application_id
) m 
USING (application_id, exited_on)
JOIN applications a ON a.application_id = s.id
WHERE a.application_id IN ('91649746', '91991364', '96444221')

这篇关于将表联接到子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！