多对多的关系和复杂的陈述

时间：2023-01-02 20:14:18

i have 3 tables, that form a many-to-many relationship between [monsters] and [types]

我有3个表,形成[怪物]和[类型]之间的多对多关系

monsters, monsters_types, types

i have the following monsters with several related [types] each

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

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

I want to find all monsters that have a have a relationship with the type "(wolf or insect) AND (human)", so with that expression i want to get

我想找到所有与“(狼或昆虫)和(人类)”类型有关系的怪物,所以我希望得到这个表达式

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

here's my query below:

这是我的查询:

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'

this won't work because there's an AND operator the type.name field can't be both 'human' and something else at the same time.

这是行不通的,因为有一个AND运算符,type.name字段不能同时是'human'和其他东西。

I've looked into using IN statments but that doesn't work well for me due to the fact that I'm building queries dynamically based on expressions passed in. For the above table an example url would be

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

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

is it possible to select all [monsters] that have the related [types] specified by a complex expression?

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

1 个解决方案

#1

0  

Try:

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

#1

0  

Try:

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')
标签：

相关文章