Counting Distinct Records Using Multiple Criteria From Another Table In MySQL

Question

This Is Not Homework. I have changed the names of the tables and fields, for illustrative purposes only. I admit that I am completely new to MySQL. Please consider that in your answer.

The best way to illustrate the function of the query I need is like this:

I have two tables.

One table has a 0..1 to 0..n relationship to the other table.

For Simplicities Sake Only, Suppose that the two tables were Recipe and Ingredient.

One of the fields in the Ingredient table refers to the Recipe table, but may be null.

Just For Example:

I want to know the SQL for something like: How many recipes call for "Olives" in the amount of "1" AND "Mushrooms" in the amount of "2"

Being brand new to The Structured Query Language, I'm not even sure what to google for this information.

Am I on the right track with the following?:

SELECT COUNT(DISTINCT Recipe.ID) AS Answer FROM Recipe, Ingredient 
  WHERE Ingredient.RecipeID=Recipe.ID AND Ingredient.Name='Olives'
  AND Ingredient.Amount=1 AND Ingredient.Name='Mushrooms'
  AND Ingredient.Amount=2

I realize this is totally wrong because Name cannot be BOTH Olives And Mushrooms... but don't know what to put instead, since I need to count all recipes with both, but only all recipes with both.

How can I properly write such a query for MySQL?

Answer 1

You were close.

Try something like

SELECT  COUNT(Recipe.ID) Answer
FROM    Recipe INNER JOIN
        Ingredient olives ON olives.RecipeID=Recipe.ID INNER JOIN
        Ingredient mushrooms ON mushrooms.RecipeID=Recipe.ID 
WHERE   olives.Name='Olives'
AND     mushrooms.Name='Mushrooms'
AND     olives.Amount = 1
AND     mushrooms.Amount = 2

You can join to the same table twice, all you need to do is give the table an appropriate alias.

Answer 2

Use:

SELECT COUNT(r.id)
  FROM RECIPE r
 WHERE EXISTS(SELECT NULL
                FROM INGREDIENTS i
               WHERE i.recipeid = r.id
                 AND i.name = 'Olives'
                 AND i.amount = 1)
   AND EXISTS(SELECT NULL
                FROM INGREDIENTS i
               WHERE i.recipeid = r.id
                 AND i.name = 'Mushrooms'
                 AND i.amount = 2)

Answer 3

SELECT COUNT(DISTINCT Recipe.ID) AS Answer 
FROM Recipe, Ingredient as ing1, Ingredient as ing2
WHERE 
  Ing1.RecipeID=Recipe.ID AND Ing1.Name="Olives" AND ing1.Amount=1 AND 
  Ing2.RecipeID=Recipe.ID AND Ing2.Name="Mushrooms" AND ing2.Amount=2;

Hope this help

Answer 4

First of all, you use the old join syntax. INNER JOIN creates the same execution plan, but is much more clear. Then, your query is all about a plain, old condition. You just have to write them correctly! (It takes some practice, I agree.)

SELECT COUNT(DISTINCT R.ID) Answer
FROM Recipe R
INNER JOIN Ingredient I
    ON R.ID = I.RecipeID
    AND (R.Name = 'Olives' AND I.Amount = 1)
    OR (R.Name = 'Mushrooms' AND I.Amount = 2);

Here is my sample data :

mysql> SELECT * FROM Ingredient;
+------+------+--------+----------+
| ID   | Name | Amount | RecipeID |
+------+------+--------+----------+
|    1 | I1   |      1 |        1 |
|    1 | I2   |      2 |        1 |
|    1 | I3   |      2 |        1 |
|    1 | I4   |      3 |        1 |
|    1 | I1   |      1 |        2 |
|    1 | I2   |      1 |        2 |
|    1 | I3   |      3 |        2 |
|    1 | I4   |      2 |        2 |
|    1 | I1   |      2 |        3 |
|    1 | I2   |      1 |        3 |
+------+------+--------+----------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM Recipe;
+------+-----------+
| ID   | Name      |
+------+-----------+
|    1 | Mushrooms |
|    2 | Olives    |
|    3 | Tomatoes  |
+------+-----------+
3 rows in set (0.00 sec)

And my query outputs 2, as it should.

Edit: Actually, I realised my query selected wrong rows. This works correctly :

SELECT COUNT(DISTINCT R.Id) Answer 
FROM Recipe R 
INNER JOIN Ingredient I 
    ON R.ID = I.RecipeID 
WHERE 
    (R.Name = 'Mushrooms' AND I.Amount = 2) 
    OR (R.Name = 'Olives' AND I.Amount = 1);

Which outputs 2 as well.

Answer 5

Just for reference...

How many recipes call for "Olives" in the amount of "1" AND "Mushrooms" in the amount of "2"

Looking at your table structure above, you may want an associative entity to go between recipes and ingredients. This makes it a lot easier to do the kind of query you're looking for with an inner join.

In between your two tables, I would imagine something like this...

Recipe_Ingredient
-----------------
(PK, FK) RecipeID
(PK, FK) IngredientID

If you have that, then each recipe may have many ingredients, and each ingredient may be a part of many recipes. Once you have a table like that to properly associate your two tables, you can join them together and get a complete recipe. For a more complicated query like this where you have separate conditions for two recipes, I would probably sub-query it to help understanding.

SELECT SUM(RecipeCount) as RecipeCount
FROM
(
    SELECT COUNT(r.*) as RecipeCount
    FROM Recipe r
    INNER JOIN Recipe_Ingredient ri on r.ID = ri.RecipeID
    INNER JOIN Ingredient i on i.ID = ri.IngredientID
    WHERE i.Name = 'Olives' AND i.Amount = 1
UNION ALL
    SELECT COUNT(r.*) as RecipeCount
    FROM Recipe r
    INNER JOIN Recipe_Ingredient ri on r.ID = ri.RecipeID
    INNER JOIN Ingredient i on i.ID = ri.IngredientID
    WHERE i.Name = 'Mushrooms' AND i.Amount = 2
) as subTable