MySQL查询表过滤问题

Question

我一直在努力解决以下问题。

我有 3 个表:players、players_clothes 和 teams_clothes。

表玩家:

id   user   team_id1    tom    42    robo   53    bob    4

So tom and bob are both on the same team

Table players_clothes:

id     clothes_id     p_id1      13             12      35             33      45             3

Bob has clothing article 35 and 45, robo has none.

Table teams_clothes:

id    clothes_id     team_id1     35             42     45             43     55             44     65             5

This shows which teams have rights to which articles of clothing. The problem: tom is wearing an article of clothing that does no belong to his team... Let's assume this is illegal.

I'm having trouble figuring out how to capture all those who are wearing illegal clothes for a particular team.

SELECT pc.clothes_id FROM players AS p
JOIN players_clothes AS pc
ON p.id = pc.p_id
AND p.team_id = 4 GROUP BY pc.clothes_id

(我按players_clothes.clothes_id分组，信不信由你，两个玩家可以分到同一件衣服)

我认为这会导致以下集合 (13, 35, 45)

现在我想核对第 4 队实际拥有的一套衣服。

SELECT clothes_id FROM teams_clothes WHERE team_id = 4 and this return (35, 45, 55)

如何创建查询以使其返回 (13)？我已经尝试过 NOT EXISTS IN 之类的东西，但我认为 GROUP BY players_clothes.clothes_id 部分会妨碍

Answer 1

我建议

select * from A where team_id = $team_id join B on B.a_id = A.id
where not exists
(
    select 1 from C where C.clothes_id = B.clothes_id and team_id = $team_id
)

基本上，我们找到所有 As 在他们的团队中，并为每个 A 加入他们穿的所有衣服，然后仅当我们在表 C 中找不到衣服在我们团队中的指示时才返回行(这涵盖在 C 中不存在并且存在但在 C 上错误的团队中)