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

Question

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.

Answer 1

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