CODEWITHSUNDEEP
mysqlsql
rjx44
rjx44

Reputation: 389

Show recipe name if all of its ingredients are already given

I have 3 tables with this structure:

Recipe

id | recipe_name

Ingredients

id | ingredients_name

Relationship

rid | recipe_id | ingredients_id

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?

Upvotes: 2

Views: 377

Answers (2)

user359040
user359040

Reputation:

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

(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.)

Upvotes: 1

Vikram
Vikram

Reputation: 8333

we need to skip all the recipes which have ingredients other than chicken and mayonnaise, so something like below should work:

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')
and r.id not in (select r.id 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 not IN ('chicken','mayonnaise'))

Upvotes: 1

Related Questions