mysql - SQL (Mysql) NOT IN 与 OR 组合，结果错误

Question

我有两个表:

锦标赛球队:

ID TEAM TournamentID
2  Berp 7
3  Dang 7

锦标赛池团队:

ID TournamentID PoolID TeamID VersusTeamID
1  7            5      2      3

现在这个查询应该不返回任何内容，因为所有团队都在池中(teamid 和 vsteamid)

SELECT t1.id,
       t1.team
FROM   tournament_teams t1
WHERE  t1.id NOT IN(SELECT t2.id
                    FROM   tournament_pool_team t2
                    WHERE  ( t2.teamid = t1.id
                              OR t2.versusteamid = t1.id )
                           AND poolid = '5')
       AND t1.tournamentid = '7'
ORDER  BY team ASC 

我只想显示表中不存在的球队:Tournament_pool_team；团队在该表的 teamid 和 vsteamid 中指定。它需要从tournament_teams中提取未在tournament_pool_team中指定的球队。对于当前的条目，它不应返回任何内容，因为 ID 2 和 3 均在tournament_pool_team 中指定。

Answer 1

SELECT t1.id,
       t1.team
FROM   tournament_teams t1
WHERE  t1.id NOT IN(SELECT t2.teamid
                    FROM   tournament_pool_team t2
                    WHERE  t2.teamid = t1.id
                    AND poolid = '5' 
                    UNION
                    SELECT t2.versusteamid
                    FROM   tournament_pool_team t2   
                    WHERE  t2.versusteamid = t1.id
                    AND poolid = '5')
       AND t1.tournamentid = '7'
ORDER  BY team ASC 

我已根据您的上下文(您尝试实现的方式)修改了查询。您将 t1.teamid 与 t2.id 进行匹配，而您应该将 t1.id 与 t2.teamid 和 t2.versusteamid 进行匹配