Re: nested query problem

Not quite. This returns one value. In the actual "sqf_runs" table, there
are many records with user_id = 'foo'. I want one line for each where the
fse.p-erl_sub_name and fse.end_datetime values are the latest values found
in the flow_step_events_view view where the sqf_ids match.

On Thu, Sep 6, 2018 at 5:10 PM Paul Jungwirth <pj(at)illuminatedcomputing(dot)com>
wrote:

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