Use field value from a subquery in another subquery where whit generate_series in PostgreSQL

Question

I need to select and Id from the data table, but where the Account is equal to the Account selected by the first subquery. Is that possible? I'm using generate_series because I need to populate a table with random data and I tried the query below but I get the error:

ERROR: missing FROM-clause entry for table "roter"

select
    (select "Account" FROM "AccountSamples" WHERE gen=gen ORDER BY random() LIMIT 1) roter,
    (select "Id" from "data" WHERE "Account" = "roter"."Account" LIMIT 1 )
from generate_series(1, 100) gen;

GMB · Accepted Answer · 2020-09-18T14:30:08.877

You probably want a lateral join:

select s.*, d.*
from generate_series(1, 100) x(gen)
cross join lateral (
    select s."Account" as roter
    from "AccountSamples" s
    where x.gen = x.gen
    order by random() 
    limit 1
) s
cross join lateral (
    select "Id" 
    from "data" d
    where d."Account" = s.roter 
    limit 1 
) d

Notes:

I added/modified table aliases to make the query easier to follow
In the first subquery, condition where x.gen = x.gen does not seem to make sense; I left it in the query (you might be trying to generate entropy to force Postgres to reexecute the query for each and every row...)
In the second subquery, limit without order by is not good practice (this is not stable, nor random)

I added the order by random() to the second subquery as you suggested. And about the x.gen, it is being used to force Postgres to reexecute the query as you noted. Thanks! — user33276346, Sep 18 '20 at 14:49

Use field value from a subquery in another subquery where whit generate_series in PostgreSQL

1 Answers1