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'

 

[naudiz]

Many thanks, that worked perfectly