Search Postgresql Archives

Re: nested query problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux