How to select a row which has one coloumn with two different attributes?

Question

I'm doing a project about cooking recipes with PHP(with Codeigniter) and MYSQL.

I have three tables:

• Ingredients - id, name.
• Recipe - id, name
• ing_to_rep - recipe_id, ingredient_id (I use this table for holding which recipe has which ingredients.)

What is the query for "get all recipes which have eggs (id = 64) and salt (id = 65)"

I tried:

SELECT * FROM recipe JOIN ing_to_rep ON recipe.id = ing_to_rep.rep_id 
WHERE ing_to_rep.ing_id = 64 AND ing_to_rep.ing_id = 65

Naturally, it returns nothing but it helps you to get what I'm trying to do.

Answer 1

There is probably a more efficient way and a more flexible way, but two subquery joins will do:

SELECT
  recipe.*
FROM recipe
  JOIN (SELECT recipe_id FROM ing_to_rep WHERE ingredient_id = 64) AS ing1 ON recipe.id = ing1.recipe_id
  JOIN (SELECT recipe_id FROM ing_to_rep WHERE ingredient_id = 65) AS ing2 ON recipe.id = ing2.recipe_id

Also can be done with EXISTS

SELECT 
  recipe.*
FROM 
  recipe
WHERE
  EXISTS (SELECT recipe_id FROM ing_to_rep ing1 WHERE ingredient_id = 64 AND recipe.id = ing1.recipe_id)
  AND  EXISTS (SELECT recipe_id FROM ing_to_rep ing2 WHERE ingredient_id = 65 AND recipe.id = ing2.recipe_id)