Multiple row conditions in WHERE clause

Question

I am having trouble writing the following query in MySQL. I have a table called pizz0r_pizza_ingredients which looks something like this:

| id | pizza_id | ingredient | amount | measure |
+----+----------+------------+--------+---------+
| 6  |    1     |    15      |   3    |    4    |
|178 |    17    |    1       |   160  |    1    |
| 3  |    1     |    20      |   3    |    4    |

I want to search for pizzas where the ingredients have specific requirements such as the following:

SELECT `pizza_id` 
FROM `pizz0r_pizza_ingredients` 
WHERE `ingredient` = 15 AND `ingredient` != 20 
GROUP BY `pizza_id`

I am trying to get entries where ingredient is equal to 15, but ignores that pizza_id if it also has ingredient 20.

The current result is 1, but in this example nothing should be returned.

Just so I understand you correctly, your scenario is something like "tell me all pizzas that have ingredient 15 (say, peppers), but not ingredient 20 (say, peperoni) or ingredient 21 (say, ham)"? — Rowland Shaw, Dec 29 '14 at 14:14
I think you should clarify that your current result is `1` but you don't want that as your result, right? Because `1` has ingredient 20? — AdamMc331, Dec 29 '14 at 14:29
Just making sure I understood. I may edit the question to be a little more clear. — AdamMc331, Dec 29 '14 at 14:33
@Mcadam331 I wanted to edit it but you were faster, Thank you — Stephan Wahlen, Dec 29 '14 at 14:40

Saharsh Shah · Answer 1 · 2014-12-29T14:20:13.983

2

Try this:

SELECT P1.pizza_id 
FROM pizz0r_pizza_ingredients P1 
LEFT OUTER JOIN pizz0r_pizza_ingredients P2 ON P1.pizza_id = P2.pizza_id AND P2.ingredient IN (20, 21)
WHERE P1.ingredient = 15 AND P2.id IS NULL
GROUP bY P1.pizza_id;

edited Dec 29 '14 at 14:20

answered Dec 29 '14 at 14:14

Saharsh Shah

28,687
8
48
83

Doesn't the filter on p2.id being null in the where clause create an implicit inner join? – Dan Bracuk Dec 29 '14 at 14:18
1

@DanBracuk It won't create inner join. It will fetch unmatching records from A table to B table – Saharsh Shah Dec 29 '14 at 14:19
Thank you so mucht, this works now. Although i dont completele understand it, Ill use this one. – Stephan Wahlen Dec 29 '14 at 14:42

Gordon Linoff · Accepted Answer · 2014-12-29T14:45:39.757

2

I like to handle these problems using group by and having:

SELECT `pizza_id` 
FROM `pizz0r_pizza_ingredients` 
GROUP BY `pizza_id`
HAVING SUM(ingredient = 15) > 0 AND
       SUM(ingredient = 20) = 0;

You can add as many new requirements as you like. The SUM() expression counts the number of ingredients of a certain type. The > 0 means that there is at least one on the pizza. The = 0 means that there are none.

edited Dec 29 '14 at 14:45

answered Dec 29 '14 at 14:40

Gordon Linoff

1,242,037
58
646
786

Never used `SUM` like this. Thanks. – SouravA Dec 29 '14 at 14:52
Thanks @Gordon Linnof this one seems to be more elegant and faster than Saharsh Shah's solution too! – Stephan Wahlen Dec 29 '14 at 14:54

score 0 · Answer 3 · answered Dec 29 '14 at 14:18

You need to combine the two sets of data - everything you might want, and then exclude those that intersect your unwanted ingredients, so something like:

SELECT DISTINCT
            wanted.pizza_id

FROM        pizz0r_pizza_ingredients wanted 

LEFT JOIN   pizz0r_pizza_ingredients dontwant
    ON      wanted.pizza_id = dontwant.pizza_id
    AND     dontwant.ingredient IN ( 20, 21 )

WHERE       wanted.ingredient IN ( 15 ) -- You could have multiples, if you wanted ham OR chicken, for example
    AND     dontwant.pizza_id IS NULL -- No bad ingredients

This also works and looks cleaner to me Thank you so much, you guys are fast! — Stephan Wahlen, Dec 29 '14 at 14:45

SouravA · Answer 4 · 2014-12-29T14:36:06.780

0

EDIT: Your question was unclear earlier. If you wanted to exclude pizzas with ingredient 20, you can use the below:

select pizzaid from pizz0r_pizza_ingredients where ingredient = 15
EXCEPT
select pizzaid from pizz0r_pizza_ingredients where ingredient != 20

NOTE: EXCEPT is generally faster than join or not in

edited Dec 29 '14 at 14:36

answered Dec 29 '14 at 14:22

SouravA

5,147
2
24
49

Thanks, but thats not quite what i wanted. I dont want This OR that i want both requirements on one Pizza_id. Its kinda hard for me to describe. – Stephan Wahlen Dec 29 '14 at 14:35
Im gettin mysql error #1064 with this one, i guess my version does not recognize EXCEPT. Thanks anyway, i got a working suggestion above. – Stephan Wahlen Dec 29 '14 at 14:44
I don't think MySQL supports EXCEPT at all. – AdamMc331 Dec 29 '14 at 14:45
1

@StephanWahlen, I was wrong, MySQL doesn't support `EXCEPT`. Thanks @McAdam331 for pointing that out. Its a shame though. – SouravA Dec 29 '14 at 14:50

score 0 · Answer 5 · edited May 23 '17 at 11:50

One option you have is to use subqueries for this. To understand this best, I would first separate the two queries and then combine them.

The first one will pull all pizza_id that have ingredient 15:

SELECT DISTINCT pizza_id
FROM myTable
WHERE ingredient = 15;

Then, you can write another one that searches for pizza_id where ingredient is equal to 20:

SELECT DISTINCT pizza_id
FROM myTable
WHERE ingredient = 20;

Last, you can combine the two by using the NOT IN clause. In other words, you can select all pizza_id that meet the first condition, where the id is NOT IN the pizza_ids that meet the second condition:

SELECT DISTINCT pizza_id
FROM myTable
WHERE ingredient = 15 AND pizza_id NOT IN(
  SELECT DISTINCT pizza_id
  FROM myTable
  WHERE ingredient = 20);

EDIT

Here is an SQL Fiddle.

EDIT 2

This is a subject known as relational division. There is another example similar to this here.

Nah, that will not return `1` – SouravA Dec 29 '14 at 14:28 — SouravA, Dec 29 '14 at 14:28
@Sourav_Agasti it's not supposed to return `1`. – AdamMc331 Dec 29 '14 at 14:45 — AdamMc331, Dec 29 '14 at 14:45

Multiple row conditions in WHERE clause

5 Answers5