CODEWITHSUNDEEP
sqlpostgresql
iamyojimbo
iamyojimbo

Reputation: 4679

Postgres Reference a subquery result in where clause

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?

Upvotes: 0

Views: 554

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246473

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.

Upvotes: 2

Related Questions