CODEWITHSUNDEEP
mysqlsql
Duane
Duane

Reputation: 75

SQL Query With & Without

I have two tables as follows:

table: recipe

fields: recipe_ID, title

table: recipe_ingredient

fields: recipe_ID, ingredient_ID

I would like to show only recipes which contain certain ingredients (I managed to do that part), however I also want to exclude recipes which contain certain ingredients.

So far I managed to do this query, it is working but it only shows recipes which contain certain ingredients.

SELECT DISTINCT r.recipe_ID, r.title 
FROM recipe r 
JOIN recipe_ingredient ri ON (ri.recipe_ID = r.recipe_ID)
WHERE ri.ingredient_ID IN (4, 7) 
GROUP BY r.recipe_ID 
HAVING COUNT(ri.ingredient_ID) = 2

How do I make it to also exclude recipes with certain ingredients? I tried some methods but I failed.

Note: The 4, 7 and Count values are static for demonstration purposes.

Please ask if you need any more info or anything.

Thanks a lot!

Upvotes: 1

Views: 142

Answers (3)

RandomSeed
RandomSeed

Reputation: 29759

SELECT receipe.*
FROM receipe

-- ingredients required
JOIN recipe_ingredient AS ingredient4
    ON ingredient4.recipe_ID = receipe.recipe_ID
    AND ingredient_ID = 4
JOIN recipe_ingredient AS ingredient7
    ON ingredient7.recipe_ID = receipe.recipe_ID
    AND ingredient_ID = 7
-- join again to add more required ingredients

-- ingredients excluded
LEFT JOIN recipe_ingredient AS ingredient9
    ON ingredient9.recipe_ID = receipe.recipe_ID
    AND ingredient_ID = 9
LEFT JOIN recipe_ingredient AS ingredient12
    ON ingredient12.recipe_ID = receipe.recipe_ID
    AND ingredient_ID = 12
-- left join again to add more excluded ingredients

WHERE
    ingredient9.ingredient_ID IS NULL
    AND ingredient12.ingredient_ID IS NULL
    -- add one "IS NULL" condition for each additional ingredient to exclude

Upvotes: 0

Linger
Linger

Reputation: 15048

You could use a sub query that gets any recipe_ID which contains the ingredients you don't want and then exclude those recipe_IDs in the main query:

SELECT r.recipe_ID, r.title 
FROM recipe r 
JOIN recipe_ingredient ri ON ri.recipe_ID = r.recipe_ID
WHERE ri.ingredient_ID IN (4, 7) 
AND r.recipe_ID NOT IN 
(
  SELECT rs.recipe_ID
  FROM recipe rs
  JOIN recipe_ingredient ris ON ris.recipe_ID = rs.recipe_ID
  WHERE ris.ingredient_ID IN (8, 2) 
)
GROUP BY r.recipe_ID 
HAVING COUNT(ri.ingredient_ID) = 2

Upvotes: 0

ZeRaTuL_jF
ZeRaTuL_jF

Reputation: 592

You can just rewrite this part of your query.

   WHERE ri.ingredient_ID not in (4,7)

I believe this is what you are looking for since you say your query returns recipes with only certain ingredients.

Upvotes: 0

Related Questions