连接三个连接表

Question

我的头都痛了!

我有三张表还又团队游戏

两个列的三个连接表。-player_teams-teams_games-player_games

我需要列出所有的球员谁是在一个游戏(例如:Game_id = 111从变量)，没有分配一个团队(在这个游戏)。我称他们为孤儿玩家

基本上是获取游戏中的团队，获取他们的玩家并与Games_players进行反向匹配。或者我想正好相反。

我试了两天，没有运气!

谢谢!

/Jp.s.在我发布这个之后，我得到了这个，但它似乎很复杂!

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 1

当游戏给定时，你可以只查看游戏中的玩家和游戏中各队的玩家:

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
);