Search code examples
mysqlsqljoingroup-byrelational-division

Multiple row conditions in WHERE clause

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.

Solution

  • 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.