Joining three junction tables

my heads hurt from trying to figure this out!

I have three tables -Players -Teams -Games

Three junction tables with two columns. -player_teams -teams_games -player_games

I need to list all Players who are in a Game (eg: Game_id = 111 from variable) that are not assigned a Team(in this game). i call them orphaned players

Basically get Teams that are in the game, get their Players and reverse match against Games_players. or the other way round i suppose.

i tried for two day no luck!

thanks!

/J p.s after i posted this i got this far but it seems to complex!

SELECT * from players
JOIN
(SELECT DISTINCT games_players.player_id from games_players
Left JOIN
(Select team_players.player_id p1 from team_players 
inner join (Select * from games_teams where games_teams.game_id = :P1) AS tm1 ON team_players.team_id = tm1.team_id) As f1
On games_players.player_id = f1.p1
where p1 is null) as q1
on players.player_id = q1.player_id

Answer

As the game is given, you can just look at the players in the game and the players of the teams in the game:

select *
from players
where player_id in
(
  select player_id
  from player_games
  where game_id = 111
)
and player_id not in
(
  select pt.player_id
  from player_teams pt
  join teams_games tg using (team_id)
  where tg.game_id = 111
);

Advertisement

Answer