0

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.

Kiki
  • 75
  • 1
  • 1
  • 7

2 Answers2

0

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.

erik258
  • 14,701
  • 2
  • 25
  • 31
  • Nope, that will return recipe_id(s) that contains other ingridients too. I want to return recipe_id(s) from the ingredients that have been given. Thanks for asnwering though. Eg. The user may have 30 ingredients in his fridge, I want to return recipes that contains these ingredients only and subsets of them, no any recipe that may contain one or more ingredients that the user doesn't have. – Kiki Jan 01 '15 at 17:28
0

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
G B
  • 1,412
  • 10
  • 12
  • Yes you understanding is right, the code works perfectly but I can't understand fully your code. I lost you on the left join. Although I got the answer I was searching for I would like to understand the code. If you have time please leave a comment explaining this solution. Thanks a lot and Happy New Year. – Kiki Jan 01 '15 at 18:46
  • 1
    If you would like to understand joins better, this is a good post http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins/16598900#16598900. specifically, I use the left join and a check for null to exclude any recipe that has ingredients that you don't have. "not in (list)" hope that helps... Happy NY 2u2 :-) – G B Jan 01 '15 at 19:08
  • Thanks a lot for the answer and refering me to understand it. – Kiki Jan 01 '15 at 19:12