MySQL return rows with multiple values in one column?

Question

this is my scenario: I have a table that holds recipes, and another that holds ingredients linked to a recipe (recipe_ingredients).

TABLE RECIPE_INGREDIENTS:

ID      RECIPE_ID     INGREDIENT_ID
1           1              6
2           2              3
3           2              4

in my case, I want to return all recipes with some ingredients introduced by user (could be one or more), in this example let's say all recipes with ingredients 3 and 4. Obviusly when I only query for one ingredient there's no problem, but I don't know how achieve that with more than one... Thanks all in advance! any advice is welcome!

Taryn · Accepted Answer · 2013-01-09 18:47:28Z

4

It sounds like you want this:

select RECIPE_ID
from RECIPE_INGREDIENTS
where INGREDIENT_ID in (3, 4)
group by RECIPE_ID
having count(distinct INGREDIENT_ID ) >=2

See SQL Fiddle with Demo

Then if you want to join this to you recipe table:

select r.*
from recipes r
inner join
(
  select RECIPE_ID
  from RECIPE_INGREDIENTS
  where INGREDIENT_ID in (3, 4)
  group by RECIPE_ID
  having count(distinct INGREDIENT_ID ) >=2
) ri
  on r.RECIPE_ID = ri.RECIPE_ID

answered Jan 9, 2013 at 18:47

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Muhammad Raheel · Accepted Answer · 2013-01-09 18:48:44Z

3

Assuming you have 3 tables recipes , ingradients and recipe_ingrediants

SELECT 
    r.*,
    GROUP_CONCAT(i.ingradients)
FROM recipes as r
LEFT JOIN recipe_ingrediants as ri ON ri.recipe_id = r.id
LEFT JOIN ingrediants as i ON r.id = ri.ingrediant_id
GROUP BY r.id

answered Jan 9, 2013 at 18:48

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Collectives™ on Stack Overflow

MySQL return rows with multiple values in one column?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related