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