Jack McAnulty Jack McAnulty - 1 month ago 9
MySQL Question

SQL query - Find the names of those ingredients of which we used a total of 4 or more teaspoons across all recipes in the database

I've been stuck on this for a while.

Find the names of those ingredients of which we used a total of 4 or more teaspoons across all recipes in the database.

At all times, underlined means that this attribute or these attributes are the primary key for the table. An attribute in italic and bold refers to an attribute that is a foreign key pointing to another table.

I've been trying things along the lines of...

SELECT name
FROM ingredient
JOIN recipe_ingredient ON ingredient.id = recipe_ingredient.ingredient_id
JOIN measurement ON recipe_ingredient.measurement_id = measurement.id
WHERE description = "teaspoon"
AND amount >4;


But I'm not entirely sure if I'm getting the correct result. I'm getting a list of ingredients but who knows.

Any guidance would be greatly appreciated.

Results looking like -

NAME
Salt
Black Pepper
Salt
Nutmeg
Cinnamon
Lime Juice
Black Pepper

etc etc... I know i can add distinct to remove the doubles

Heres a sample of my table when i enter

SELECT *
FROM ingredient
JOIN recipe_ingredient ON ingredient.id = recipe_ingredient.ingredient_id
JOIN measurement ON recipe_ingredient.measurement_id = measurement.id


Table Exert

Answer

I understand that we're looking for ingredients that were used at least 4 teaspoons in all recipies, not the recipes that used 4 teaspoons

SELECT i.name
FROM ingredient i
JOIN recipe_ingredient ri ON i.id = ri.ingredient_id
JOIN measurement m ON ri.measurement_id = m.id
WHERE m.description = "teaspoon"
group by i.name
having sum(ri.amount) >= 4;
Comments