MySQL select from multiple records problem

[naudiz]

Hey all, having trouble with this query. I'm trying to select recipes that match all the ingredients specified. Although the query works with one ingredient, as soon as you add multiple ingredients it doesn't return any results. I'd appreciate it if someone could look over the following query and give me any pointers as to why it's not working.

To try and get around the issue of selecting multiple ingredients for a recipe, I tried inner joining to the same table and creating a different instance of it each time - t2, t3 etc...

SELECT  `recipe`.`recipe_id` ,  `recipe`.`name` ,  `t2`.`name` ,  `t3`.`name` 
FROM  `recipe` 
INNER JOIN  `ingredients_in_recipes` ON  `recipe`.`recipe_id` =  `ingredients_in_recipes`.`recipe_id` 
INNER JOIN  `ingredients`  `t2` ON  `ingredients_in_recipes`.`ingredients_id` =  `t2`.`ingredients_id` 
INNER JOIN  `ingredients`  `t3` ON  `ingredients_in_recipes`.`ingredients_id` =  `t3`.`ingredients_id` 
WHERE  `t2`.`name` =  'carrot'
AND  `t3`.`name` =  'caster sugar'

 

[fez]

The structure of your tables would be useful but essentially you are wanting to do:

SELECT r.r_id , r.name
FROM recipe r
INNER JOIN ingredients_in_rs rs ON r.r_id = rs.r_id
INNER JOIN ingredients i ON rs.ingredients_id = i.ingredients_id
WHERE i.name in ('carrot', 'chicken', 'broccoli')
GROUP BY r.r_id ORDER BY count(i.name) desc

That will get all recipies that contain any of the ingredients specified and then decide which recipe is the best fit based on the number of ingredients used out of those entered. Its a rough query but it gives you the gist of it.

 

[naudiz]

Many thanks, that worked perfectly

 

[fez]

Madness! I was waiting for you to come back and ask me what I was chatting about.

Glad to help.