CODEWITHSUNDEEP
sqlsql-server
Nick
Nick

Reputation: 3

Join query using three tables and display missing records

I have three tables in a database that I am querying:

players

Player_id Player_Name
1 Tom
2 Dick
3 Harry

games

Game_id Game_Name
1 Tennis
2 Rugby
3 Cricket

Games_Players

game_id player_id Date_Played
1 2 2021-12-20
1 3 2021-12-20
2 3 2021-12-21
3 2 2021-12-22
3 3 2021-12-22

I want a query that will return the players id, name, game name and Date Played. If they have not played a game - as in the case of Tom (1) and Dick (2) - then I want the id, player name, game name and a null value.

Desired Result

player_id player_name game_name Date_Played
1 Tom Tennis null
1 Tom Rugby null
1 Tom Cricket null
2 Dick Tennis 2021-12-20
2 Dick Rugby null
2 Dick Cricket 2021-12-22
3 Harry Tennis 2021-12-20
3 Harry Rugby 2021-12-21
3 Harry Cricket 2021-12-22

I've tried the below query, which is closest I can get, and I've tried other joins, but I can't seem to get all the data I need:

SELECT players.player_id, players.player_name, games.game_name, Games_Players.Date_Played,
FROM players
LEFT OUTER JOIN Games_Players
LEFT OUTER JOIN games
ON players.player_id = Games_Players.player_id
AND Games_Players.game_id=games.game_id

It's not returning all the records I need.

Upvotes: 0

Views: 658

Answers (2)

georgoulis
georgoulis

Reputation: 1

It seems that you have not add the columns for the first join with table Game_Players. Also, you have a comma after the last column in select section. So, your query should be:

SELECT players.player_id, 
players.player_name, 
games.game_name, 
Games_Players.Date_Played
FROM players
LEFT OUTER JOIN Games_Players 
ON players.id = Games_Players.player_id
LEFT OUTER JOIN games
ON players.player_id = Games_Players.player_id
AND Games_Players.game_id=games.game_id

Upvotes: 0

persian-theme
persian-theme

Reputation: 6638

Use cross join to join the players table to the games table and then join the result to the Games_Players table

select 
    t1.player_id,
    t1.player_name,
    t1.game_name,
    t2.Date_Played

from
  (select * 
   from players p
   cross join games g) t1
left join Games_Players t2
on t1.Player_id = t2.Player_id and t1.game_id = t2.game_id

demo in db<>fiddle

Upvotes: 2

Related Questions