CODEWITHSUNDEEP
sqlpostgresqlrandomsubquerylateral-join
user33276346
user33276346

Reputation: 1739

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

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;

Upvotes: 2

Views: 426

Answers (1)

GMB
GMB

Reputation: 222482

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)

Upvotes: 2

Related Questions