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

players

games

Games_Players

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

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.

CodePudding user response：

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

CodePudding user response：

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