mysql - 查找仅包含给定成分的食谱的匹配行

Question

这个问题已经有答案了:

mysql query to select all except something (2 个回答)

已关闭 6 年前。

我在MySql中有三个表RECIPES , INGREDIENTS和RECIPE_INGREDIENTS 。现在我想获取仅包含提供的成分 ID 的食谱。简单来说，我想搜索可以用我拥有的原料制作的食谱。

Answer 1

您可以使用以下查询来获取包含全部或部分指定成分的食谱的 ID:

SELECT r.id
FROM Recipes AS r
INNER JOIN Recipe_ingredients AS ri ON r.id = ri.recipe_id
INNER JOIN Ingredients AS i ON i.id = ri.ingredient_id
WHERE i.id IN ( ... list of ids here ...)
GROUP BY r.id
HAVING COUNT(DISTINCT i.id) <= n -- where n is the number of the ingredient ids
                                 -- specified in the list of the where clause

编辑:如果您想获取不包含指定列表中未包含的任何成分的食谱，那么您可以使用:

SELECT r.id
FROM Recipes AS r
LEFT JOIN Recipe_ingredients AS ri ON r.id = ri.recipe_id
LEFT JOIN Ingredients AS i ON i.id = ri.ingredient_id
WHERE i.id NOT IN ( ... list of ids here ...)
GROUP BY r.id
HAVING COUNT(i.id) = 0