从定义的子集中搜索列中的所有值组合

I have a table with the following schema in MySQL

Recipe_Quantity_ID,Recipe_ID, Recipe_Quantity, Ingredients_ID, Ingredient_Measurement_ID

The sample data can be found in this SQL Fiddle http://sqlfiddle.com/#!2/d05fe .

I want to search the table for the given (one or many) Ingredients_ID and return the Recipe_ID that has this Ingredients_ID

I do this by using this SQL

select Recipe_ID 
from   recipe_quantity
group by Recipe_ID 
having count(*) = {$ar_rows} 
and    sum(Ingredients_ID in ({$ids})) = {$ar_rows}

which may translate to

select Recipe_ID 
from   recipe_quantity 
group by Recipe_ID 
having count(*) = 4 
and    sum(Ingredients_ID in (8,5,45,88)) = 4

For searching for less Ingredients_ID I substract the last ID until I reach one Ingredient ID. By using this technique of course is not possible to search for all the combinations. Eg 8,5,45,85 | 8,45,85 | 45,85 | 5,45,85 etc.

Any ideas how I can search for all the combinations that may be true? Thanks in advance.

My understanding is that you want to get all recipes where you already have all the ingredients you need. you don't need to use all the ingredients you have but you don't want to have to go shopping.

Correct me if I am wrong but I don't think there is a recipe that fits your ingredients list so I have used other ingredients. note that ingredients 13,36 wont be used.

you should be able to put another select statement in the brackets that gets the ingredients that you have (select ingredients_id from ingredients_owned) it isn't good to specify them each time.

select distinct c.Recipe_id
from
  (select Recipe_ID
  from recipe_quantity 
  where Ingredients_ID in (5,6,1,11,8,12,13,36, 81,82,62,73,35)) c
  left join (select Recipe_ID
              from   recipe_quantity 
              where Ingredients_ID not in (5,6,1,11,8,12,13,36, 81,82,62,73,35)) x
    on c.Recipe_id = x.Recipe_id
where x.Recipe_id is null

How about something like this?

select Recipe_ID, group_concat(Ingredients_ID), count(*) as ingredients
from   recipe_quantity 
where Ingredients_ID IN (8,5,45,88)
group by Recipe_ID 
having ingredients > 0
order by ingredients desc

Instead of grouping all recipe ingredients and then filtering out the ones that don't include the ingredients you're looking for, I match only the entries in recipe_quantity that match the ingredients in the first place. I use a group_concat so you can see the set of ingredients that match.

This orders by the number of ingredients that match, but still preserves partial matches on one or more ingredients. You change the 0 to the minimum number of ingredients to match.