Re: nested query problem

Wow, I take that back. I thought there were many recs with "foo" but there
wa sonly one.
When I ran this against a value that actually had multiple records, it ran
fine.

Sorry for that.
And Thanks for this query !

On Thu, Sep 6, 2018 at 5:15 PM David Gauthier <davegauthierpg(at)gmail(dot)com>
wrote:

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