SQL NOT IN 解释

Question

在尝试创建一个实际上非常简单的 SQL 语句时，我发现自己迷失了方向。

我有一个包含 3 个表的数据库:

食谱 - 存储一些用于 cooking 的食谱名称

配料食谱 - 将配料与食谱链接

成分 - 食谱使用的成分。

它像这样存储以使其双重清晰:

食谱:

id | name 1 | lasagne alla bolognese 2 | tuna with tomatoes

ingredients:

id | name 1 | lasagne slices 2 | meat 3 | tomato 4 | tuna

and join-table ingredients_recipes:

ingredient_id | recipes_id            1 | 1            2 | 1            3 | 1            3 | 2            4 | 2 

So as you can see, there are 2 really undelicios recipes which I should at least give some spices. But what I want to do before is selecting recipes by ingredients.

I want to have all my recipes having meat AND tomatoes:

SELECT recipes.name FROM recipes r
INNER JOIN ingredients_recipes ir ON ir.recipes_id = r.id
WHERE ir.ingredient_id IN ( 2 ) AND ir.ingredient_id IN ( 3 )

-> 烤宽面条.. 好! (我使用 IN 因为可能有一堆成分，比如“西红柿”、“西红柿”、“西红柿、切片”等。)

当我想拥有例如所有有西红柿但没有金枪鱼的食谱，我试过:

SELECT recipes.name FROM recipes r
INNER JOIN ingredients_recipes ir ON ir.recipes_id = r.id
WHERE ir.ingredient_id IN ( 2 ) AND ir.ingredient_id NOT IN ( 4 )

-> 仍然得到金枪鱼 - 因为我加入的行之一不包含成分 4。好的:/

我现在想知道的是，我必须做什么才能得到我想要的结果。
我目前通过这样的子选择在我的膝盖上放了一个箭头:

SELECT recipes.name FROM recipes r
INNER JOIN ingredients_recipes ir ON ir.recipes_id = r.id
WHERE (
        ir.ingredient_id IN ( 2 ) -- or more..
        AND 
        recipes.id NOT IN ( SELECT recipes_id FROM ingredients_recipes
                             WHERE ingredient_id IN ( 4 ) -- actually i paste names of the ingredients.. but that is not the case. just to shorten the query.. by filling in a comment twice as long..
                            )
      )

因为我对 mySQL 很陌生，所以我什至不知道要谷歌搜索什么。所以在这种情况下的任何帮助，并且o/c更好的SQL语句将是..

 IN(awesome).

编辑:啊，是的，我实际上是在分组.. ;-)

Answer 1

这是一种方式。 (未经测试)

SELECT r.name 
FROM recipes r
JOIN ingredients_recipes ir 
  ON ir.recipe_id = r.id
WHERE EXISTS ( SELECT *
    FROM ingredients_recipes ex
    WHERE ex.recipe_id = r.recipe_id
    AND ex.ingredient_id IN ( 2 , 3 )
    GROUP BY nx.recipe_id
    HAVING COUNT(*) = 2
    );

count(*) 为 2 的唯一方法是如果 2 和 3 都存在。

另一种方法可能是:(仍未测试)

SELECT r.name 
FROM recipes r
JOIN ingredients_recipes ir 
  ON ir.recipe_id = r.id
WHERE EXISTS ( SELECT *
    FROM ingredients_recipes ex
    WHERE ex.recipe_id = r.recipe_id
    AND ex.ingredient_id = 2
    )
AND EXISTS ( SELECT *
    FROM ingredients_recipes ex
    WHERE ex.recipe_id = r.recipe_id
    AND ex.ingredient_id = 3
    );

更新:(我误读了这个问题)如果您也不希望出现具有特定成分的食谱，您可以在查询中添加另一个子查询“leg”:

... AND NOT EXISTS ( SELECT *
    FROM ingredients_recipes ex
    WHERE ex.recipe_id = r.recipe_id
    AND ex.ingredient_id IN ( 4,5 )
    );