I’m trying to build code to get recipe codes from those recipes containing some quantities of some ingredients.
table is:
recipe_code | recipe_name | ingredient | ingredient_quantity
I’d like to get all recipes containing between 100 and 200 grams of rice and, at the same time, between 1 and 2 grams of salt; but only those recipes fulfilling both requirements at the same time.
In the case you presented the request is: "SELECT recipe_code FROM table_name WHERE (ingredient=rice AND ingredient_quantity BETWEEN 100 AND 200) AND (ingredient=salt AND ingredient_quantity BETWEEN 1 AND 2)
More indicated is to have 3 tables:
table1: recipes
recipe_code|recipe_name
table2: ingredients
ingredient_code|ingredient_name
table3: recipes_ingredients
recipe_code|ingredient_code|ingredient_quantity
In this case the request is: “SELECT r.recipe_code FROM recipes r, ingredients i, recipes_ingredients ri WHERE (r.recipe_code=ri.recipe_code AND r.ingredient_code=ri.ingredient_code) AND (i.ingredient_name=‘rice’ AND ri.ingredient_quantity BETWEEN 100 AND 200) AND (i.ingredient_name=‘salt’ AND ingredient_quantity BETWEEN 1 AND 2)”
the WHERE clause is evaluated on each row separately – and there’s no way on earth that the same ingredient_name value can be equal to two separate things at the same time
Yes, you’re right, sorry for that mistake, i put a query not tested:
So, here are the tables:
table1: recipes
recipe_code|recipe_name
table2: ingredients
ingredient_code|ingredient_name
table3: recipes_ingredients
recipe_ingredient_code|recipe_code|ingredient_code|ingredient_quantity
…and here is the query, tested and working:
SELECT r.recipe_code FROM recipes r, ingredients i, recipes_ingredients ri WHERE (r.recipe_code=ri.recipe_code AND i.ingredient_code=ri.ingredient_code) AND (i.ingredient_name=‘salt’ AND ri.ingredient_quantity BETWEEN 1 AND 2) AND r.recipe_code IN (SELECT r.recipe_code FROM recipes r, ingredients i, recipes_ingredients ri WHERE (r.recipe_code=ri.recipe_code AND i.ingredient_code=ri.ingredient_code) AND (i.ingredient_name=‘rice’ AND ri.ingredient_quantity BETWEEN 100 AND 200))
i prefer without the recipe_ingredient_code column
and the query is like this –
SELECT r.recipe_code
FROM recipes AS r
INNER
JOIN recipes_ingredients AS ri
ON ri.recipe_code = r.recipe_code
INNER
JOIN ingredients AS i
ON i.ingredient_code = ri.ingredient_code
WHERE ( i.ingredient_name='salt' AND ri.ingredient_quantity BETWEEN 1 AND 2 )
OR ( i.ingredient_name='rice' AND ri.ingredient_quantity BETWEEN 100 AND 200 )
GROUP
BY r.recipe_code
HAVING COUNT(*) = 2