Postgres Reference a subquery result in where clause

Question

I have written this query:

select *
from user.bet_lang_translation trans
inner join (
    select id, fixture_id, name, price, status
    from user.bet
    where bet.status = 'open'
) res
on trans.bet_id = res.id

The result is this:

|bet_id           |name     |bits                 |id               |fixture_id|name|price   |status|
|-----------------|---------|---------------------|-----------------|----------|----|--------|------|
|14861177884454004|goal_diff|000000000010000000000|14861177884454004|4454004   |X   |4.000000|open  |
|14861177184454004|goal_diff|111111111100000000000|14861177184454004|4454004   |2   |1.750000|open  |
|14861177174454004|goal_diff|000000000001111111111|14861177174454004|4454004   |1   |4.200000|open  |

My aim is to now join the results of this query with itself, twice so that I can make unique combinations of all bet_id for each fixture. Maybe this would work with CROSS JOIN too.

For getting one combination, I tried to add this to the bottom of the query:

inner join res as a
on res.fixture_id = a.id

but I get an error: [42P01] ERROR: relation "res" does not exist.

I understand that this is because the two parts of the query are executed in parallel so can't cross reference. I have read about LATERAL but not sure if that will help me here.

Any ideas how I can achieve what I need here?

so I already have a working query using one of those, but I feel like there's a more succinct way to achieve what I need. Is there not? — iamyojimbo
– iamyojimbo, Commented Jul 16, 2019 at 13:03
Problem is sub-queries is are scoped. I.e. the subquery exists only where you specify it. You have to hoist it to the top level using a with clause to freely re-use it — Avin Kavish
– Avin Kavish, Commented Jul 16, 2019 at 13:07

Laurenz Albe · Accepted Answer · 2019-07-16 16:14:46Z

2

To spell out how a CTE could do what you want:

WITH res AS (
   SELECT id, fixture_id, name, price, status
   FROM user.bet
   WHERE bet.status = 'open'
)
SELECT *
FROM user.bet_lang_translation trans
   JOIN res res1 ON trans.bet_id = res1.id
   JOIN res res2 ON res1.fixture_id = res2.id;

The CTE will be calculated only one and then materialized, which is efficient if you want to use it twice.

answered Jul 16, 2019 at 16:14

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iamyojimbo Over a year ago

I'll mark this as correct if there's no other way to do it without a CTE

Collectives™ on Stack Overflow

Postgres Reference a subquery result in where clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related