显示配方名称(如果已提供所有成分的名称) [英] Show recipe name if all of its ingredients are already given

问题描述

我有3个具有这种结构的表:

I have 3 tables with this structure:

食谱

id |食谱名称

成分

id | Ingredients_name

id | ingredients_name

关系

rid | recipe_id | Ingredients_id

rid | recipe_id | ingredients_id

示例i搜索成分为鸡肉"和蛋黄酱"的食谱，然后它将显示所有包含这两种成分的食谱.我已经获得了代码:

Example i search for recipe's with an ingredient of "chicken" and "mayonnaise" then it will display all the recipe's having this two ingredients. i already get the code for that:

SELECT * from recipe r
INNER JOIN relationship ri on r.id=ri.recipe_id
INNER JOIN ingredients i on i.id=ri.ingredients_id
WHERE i.ingredients_name IN ('chicken','mayonnaise')
GROUP BY r.id

但是我想要的是，当一个食谱仅包含鸡肉和蛋黄酱作为其成分时，它必须在单独的结果中显示.我该怎么办?

But what I want is that when a recipe contains only chicken and mayonnaise as its ingredients, it must display on a separate result. How can i do that?

推荐答案

通过添加HAVING子句-像这样:

By adding a HAVING clause - like so:

SELECT * from recipe r
INNER JOIN relationship ri on r.id=ri.recipe_id
INNER JOIN ingredients i on i.id=ri.ingredients_id
GROUP BY r.id
HAVING COUNT(DISTINCT i.ingredients_name)=2 AND
       COUNT(DISTINCT case when i.ingredients_name IN ('chicken','mayonnaise') 
                           then i.ingredients_name end)=2

(注意:这将返回仅包含鸡和蛋黄酱且不包含其他成分的食谱.要返回包含鸡和蛋黄酱但也可能包含其他成分的食谱，请移除COUNT(DISTINCT case条件并恢复WHERE子句.)

(Note: this will return recipes that only include both chicken and mayonnaise, and no other ingredients. To return recipes that include both chicken and mayonnaise, but that could also include other ingredients, remove the COUNT(DISTINCT case condition and reinstate the WHERE clause.)

这篇关于显示配方名称(如果已提供所有成分的名称)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！