Reporting all recipes containing certain amounts of some ingredients

[Atzen]

Hi,

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.

I'd appreciate your help.

Atzen

[r937]

you actually repeat the recipe code and recipe name for every single ingredient in a recipe?

[groody_son]

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)"

[Atzen]

you actually repeat the recipe code and recipe name for every single ingredient in a recipe?

Yes, I do.
Actually what I have is a table resulting from the union of the three tables suggested by groody_son. I thought it'd be easier that way.

Thanks groody_son, I'll try your second approach.

[r937]

WHERE ...
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)"

this will ~always~ return zero rows

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

what's needed here is GROUP BY and HAVING

[groody_son]

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))

[r937]

So, here are the tables:
table3: recipes_ingredients
recipe_ingredient_code|recipe_code|ingredient_code|ingredient_quantity

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