mysql select having multiple n to n's

I have tables resembling this

recipes_tbl
| id | Recipe | Directions |

ingredients_tbl
| id | Ingrediant | Category |

recipe_to_ingr开发者_C百科edient
| id | id_recipe  | id_ingredient | Quantity

I'm trying to build a query to "Get all recipes that use beef(id_ingredient 1) and potatoes(id_ingredient 2) and ...n"

I suspect the solutios involves some kind of fancy joins... maybe?

SELECT     R.ID, R.Recipe, R.Directions
FROM       Ingredients_tbl I
           INNER JOIN recipe_to_ingredient RI ON I.id = RI.id_ingredient 
           INNER JOIN recipes_tbl R ON R.id = R.id_recipe  
WHERE      I.ID IN (1 ,2)
GROUP BY   R.ID, R.Recipe, R.Directions
HAVING     COUNT(*) > 1

Should do it, though the ingredients in this example are hardcoded to only ever be 1 or 2. I'd need to know more about how you intend on supplying the ingredient id's to provide more on that front.

Using EXISTS:

SELECT 
    id, Recipe, Directions
FROM
    recipes_tbl AS r
WHERE EXISTS 
        ( SELECT *
          FROM recipe_to_ingredient AS ri
            JOIN ingredients_tbl AS i
              ON i.id = ri.id_ingredient
          WHERE ri.id_recipe = r.id
            AND i.Ingredient = 'beef'
       )
  AND EXISTS 
        ( SELECT *
          FROM recipe_to_ingredient AS ri
            JOIN ingredients_tbl AS i
              ON i.id = ri.id_ingredient
          WHERE ri.id_recipe = r.id
            AND i.Ingredient = 'potatoes'
       )
  ...

Using JOIN:

SELECT 
    r.id, r.Recipe, r.Directions
FROM
    recipes_tbl AS r
  JOIN 
    recipe_to_ingredient AS ri1
      ON ri1.id_recipe = r.id
  JOIN
    ingredients_tbl AS i1
      ON i1.id = ri1.id_ingredient
      AND i1.Ingredient = 'beef'
  JOIN 
    recipe_to_ingredient AS ri2
      ON ri2.id_recipe = r.id
  JOIN
    ingredients_tbl AS i2
      ON i2.id = ri1.id_ingredient
      AND i2.Ingredient = 'potatoes'
  ...

Using GROUP BY:

SELECT 
    r.id, r.Recipe, r.Directions
FROM
    recipes_tbl AS r
  JOIN 
    recipe_to_ingredient AS ri
      ON ri.id_recipe = r.id
  JOIN
    ingredients_tbl AS i
      ON i.id = ri.id_ingredient
WHERE i.Ingredient IN ('beef', 'potatoes', ...)
GROUP BY r.id
HAVING COUNT(*) = @n              --- number of items in above list 

If you already have the ids of your ingredients, no need to join to to the ingredients table (in all 3 versions).

The advantage of the GROUP BY solution is that you can alter it to show you Recipes that have for example "at least 5, out of 7 ingredients", simply changing HAVING COUNT(*)=7 to HAVING COUNT(*) BETWEEN 5 AND 7. The other two ways are not flexible in that matter.

But they may be faster, depending on your tables' sizes, distributions and the queries you run (with 2, 3 or 20 ingredients?)

SELECT     R.ID, R.Recipe, R.Directions
FROM       recipes_tbl R
WHERE      R.ID IN (SELECT id_recipe 
                    FROM   recipe_to_ingredient RI
                    INNER JOIN Ingredient I ON I.id = RI.id_ingredient
                    WHERE I.Ingredient in ('Beef','Potato'))

EDITed:

Try this query -

SELECT
  r.Recipe
FROM
  recipes_tbl r
JOIN recipe_to_ingredient r_i
  ON r.id = r_i.id_recipe
JOIN ingredients_tbl i
  ON i.id = r_i.id_ingredient
GROUP BY
  r.Recipe
HAVING
  COUNT(IF(i.Ingrediant = 'beef', 1, 0)) > 0
  AND
  COUNT(IF(i.Ingrediant = 'potatoes', 1, 0)) > 0