Reputation: 57
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
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