mysql - 多对多关系和复杂的 where 语句

Question

我有3个表，它们在[怪物]和[类型]之间形成多对多关系

monsters, monsters_types, types

我有以下怪物，每个怪物都有几种相关的[类型]

MONSTERS.name  |  TYPES.name
===============|===============
sharkthing     |  fish
sharkthing     |  human
               |
werewolf       |  human
werewolf       |  wolf
werewolf       |  warrior
               |
blob           |  ooze
               |
thefly         |  insect
thefly         |  human

我想找到所有与“(狼或昆虫)AND(人类)”类型有关系的怪物，所以用这个表达我想得到

MONSTERS.name   
===============
werewolf       
thefly        

下面是我的查询:

SELECT monsters.name from monsters
JOIN monsters_types
ON monsters_types.monster_id = monster.id
JOIN types
ON types.id = monsters_types.type_id
WHERE (types.name = 'wolf' OR types.name = 'insect') AND types.name = 'human'

这不起作用，因为有一个 AND 运算符，type.name 字段不能同时是“人类”和其他内容。

我已经研究过使用 IN 语句，但这对我来说效果不佳，因为我是根据传入的表达式动态构建查询。对于上表，示例 url 为

www.example.com/listmonsters?type=(wolf|insect),human

是否可以选择具有由复杂表达式指定的相关[类型]的所有[怪物]？

Answer 1

尝试:

SELECT DISTINCT monsters.name
FROM   monsters
       JOIN monsters_types
         ON monsters_types.monster_id = monster.id
WHERE  EXISTS (SELECT *
               FROM   types
               WHERE  types.id = monsters_types.type_id
                      AND type.name IN ( 'wolf', 'insect' ))
       AND EXISTS (SELECT *
                   FROM   types
                   WHERE  types.id = monsters_types.type_id
                          AND type.name = 'human')