CODEWITHSUNDEEP
sqlselectjoin
koryakinp
koryakinp

Reputation: 4125

LEFT JOIN same table multiple times

I have following database schema:

Players table:

| player_id | 
|-----------|
|     1     |   
|     2     |  
|     3     |   
|     4     |
|    ...    |

Games table:

| game_id  | white_player_id | black_player_id | game_status | 
|----------|-----------------|-----------------|-------------|
|    1     |        1        |        2        | in progress |    
|    2     |        3        |        4        | in progress | 
|    3     |        5        |        6        |  white won  | 
|    4     |        1        |        3        |     draw    | 
|   ...    |       ...       |       ...       |     ...     |

I want to select all players, who are not currently playing.

I tried to achieve it in the following way, but it does not work as i expect.

SELECT player.player_id
FROM players
LEFT JOIN games g1
  ON players.player_id = g1.white_player_id
LEFT JOIN games g2
  ON players.player_id = g2.black_player_id
WHERE 
  g1.white_player_id IS NULL AND
  g1.game_status NOT IN ('in progress') OR
  g2.white_player_id IS NULL AND
  g2.game_status NOT IN ('in progress')

Any help would be greatly appreciated.

Upvotes: 0

Views: 1518

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20794

When you do this:

select *
from t1 left join t2 on something
WHERE t2.someField = someValue
AND other conditions

You effectively convert the left join to an inner join. To filter on the outer joined table, do so in the from clause.

select *
from t1 left join t2 on something
AND t2.someField = someValue
WHERE other conditions

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT player_id
FROM players
WHERE player_id NOT IN (
   SELECT white_player_id
   FROM games
   WHERE game_status IN ('in progress')

   UNION ALL

   SELECT black_player_id
   FROM games
   WHERE game_status IN ('in progress'))

This query selects all players not participating in games with a game_status equal to 'in progress'.

Upvotes: 2

Related Questions