Re: nested query problem

On 09/06/2018 01:59 PM, David Gauthier wrote:
> I'm having trouble with this query...
>
> select sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
>   from
>     sqf_runs sr,
>     (select perl_sub_name, end_datetime from flow_step_events_view
> where sqf_id = sr.sqf_id order by 2 limit 1) fse
>   where sr.userid='foo';
>
> ERROR:  invalid reference to FROM-clause entry for table "sr"
> LINE 4: ...atetime from flow_step_events_view where sqf_id = sr.sqf_id ...
>                                                              ^
> HINT:  There is an entry for table "sr", but it cannot be referenced
> from this part of the query.

This calls for a lateral join:

SELECT sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime
FROM sqf_runs sr
LEFT OUTER JOIN LATERAL (
SELECT perl_sub_name, end_datetime
FROM flow_step_events_view fsev
WHERE fsev.sqf_id = sr.sqf_id
ORDER BY 2
LIMIT 1
) fse
ON true
WHERE sr.userid = 'foo'
;

It's nearly what you had already, but `LATERAL` lets the subquery
reference columns in the other tables.

A lateral join is conceptually a lot like running your subquery in for
loop, looping over all the rows produced by the rest of the query. It
doesn't have to produce 1 row for each iteration, but saying `LIMIT 1`
ensures that here.

The `ON true` is just pro forma because you can't have a join without an
`ON` clause.

You might prefer an INNER JOIN LATERAL, depending on your needs.

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com