Counting individual records using multiple criteria from another table in MySQL

Question

This is not homework. I changed the names of the tables and fields for illustrative purposes only. I admit that I am completely new to MySQL. Please consider this in your answer.

The best way to illustrate the query function I need is as follows:

I have two tables.

One table has a ratio of 0..1 to 0..n to another table.

Just for simplicity sake. Suppose the two tables were Recipe and Ingredient.

One of the fields in the table "Ingredient" refers to the recipe table, but may be zero.

For example only:

I want to know SQL for something like: How many recipes does Olives need in amount of "1" and "Mushrooms" in amount of "2"

Being completely new to Structured Query Language, I'm not even sure what to do with 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 understand that this is completely wrong, because the Name cannot be BOTH Olives and Mushrooms ... but I don’t know what to put instead, since I need to consider all recipes as both, but only all recipes with both.

How can I correctly 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 the same table twice, all you have to do is provide the alias table.

Answer 2

Using:

 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 for this help

Answer 4

First of all, you are using the old join syntax. INNER JOIN creates the same execution plan, but is much more clear. Then your request is for a simple, old state. You just need to write them correctly! (I understand, 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 are my details:

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

Edit: Actually, I realized that my query selected the 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); 

What are the outputs 2 as well.

Answer 5

For reference only ...

How many recipes does Olives require in an amount of 1 and Mushrooms in an amount of 2

By looking at your table structure above, you might need an associative object to go between recipes and ingredients. This greatly facilitates the execution of the query you are looking for with an internal join .

Between your two tables, I would suggest something like this ...

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

If you have this, each recipe can contain many ingredients, and each ingredient can be part of many recipes. When you have a table like this to properly link your two tables, you can combine them and get the complete recipe. For a more complex query like this, when you have separate conditions for two recipes, I will probably request it to help you understand.

 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