CODEWITHSUNDEEP
sqlpostgresqljoin
PaulyM
PaulyM

Reputation: 57

Left Outer Join not returning expected result

I have a two tables: player and game. I would like to return a list of all players regardless of whether their team played or not. I would like the game_id if their team did play, otherwise substitute for a NULL.

I thought this would just be a LEFT OUTER JOIN, but it is only returning a list of players that actually played.

SELECT a.id, b.match_id 
FROM player a 
  LEFT OUTER JOIN game b ON a.team_id = b.home_team_id or a.team_id = b.away_team_id 
WHERE b.round = 1

I imagine this is basic stuff .. sorry.

Upvotes: 3

Views: 128

Answers (1)

user330315
user330315

Reputation:

Your where condition on the outer table turns the outer join back into an inner join because the where will only be true for values that are not null, but rows where no match is found in the outer joined table will have all null values.

You need to move your where condition into the join condition.

SELECT a.id, b.match_id 
FROM player a 
  LEFT OUTER JOIN game b 
     ON (a.team_id = b.home_team_id or a.team_id = b.away_team_id) 
    AND b.round = 1

Upvotes: 7

Related Questions