Join table to a subquery

Question

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.)

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')

Answer 1

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')

Answer 2

Use window functions for this:

select ast.*, a.status, a.stage_name
from (select ast.*,
             rank() over (partition by application_id order by exited_on desc) as seqnum
      from application_stages ast
     ) ast join
     applications a
     on ast.application_id = a.id
where ast.seqnum = 1 and
      ast.application_id in ('91649746', '91991364', '96444221');

Your query has several issues:

• application_id in the where is ambiguous
• application_stages has the alias t1, so the former is not recognized

Note that if application_id is a number (which I'm guessing is the case), then the constants should not have single quotes).