SQL Query With & Without

Question

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!

As far I understood,what you want is list of all the recipes which has some ingredients(set A) but which don't have the some other ingredients(set B). Now set A may be a sub set/super set or completely diff sets? — avisheks, Aug 01 '14 at 13:21
I want one list, consisting of recipes which have some ingredients in them, but not having some other ingredients. Because for example, you don't like apples, so you want recipes that don't have apples in them. But it is ok now I believe I have received a good answer. Thanks for your interest guys. — Duane, Aug 01 '14 at 13:24
http://forums.mysql.com/read.php?10,507748,507839#msg-507839 — Strawberry, Aug 01 '14 at 13:25

score 0 · Answer 1 · answered Aug 01 '14 at 13:11

0

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.

answered Aug 01 '14 at 13:11

ZeRaTuL_jF

582
2
4
20

I want it to show recipes with certain ingredients, and also to exclude recipes with certain other ingredients, in the same query. I tried to add your SQL to mine, but it shows nothing, without an error. Thanks. – Duane Aug 01 '14 at 13:17

score 0 · Accepted Answer · answered Aug 01 '14 at 13:16

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

nothing really I just left it because the OP had it. It can be removed — Linger, Aug 01 '14 at 13:20

score 0 · Answer 3 · answered Aug 01 '14 at 13:24

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

@Strawberry Tedious indeed, but [likely to run faster](http://stackoverflow.com/a/477013/1446005). Such queries are typically generated, so using this form should be painless in most cases. — RandomSeed, Aug 26 '14 at 08:15

SQL Query With & Without

3 Answers3

Linked