MySQL many to many relationship matching

Question

I'm making a cocktail database, and right now I have three tables:

drinks has columns drinks_id and drinks_name
ingredients has columns ingredients_id and ingredients_name
The third is a simple relationship table called recipes that has drinks_id and ingredients_id

I want to query the database with a set of ingredient_ids and get the set of drinks available from that set of ingredients. For example, if drink A contains ingredients (1,2), drink B contains (1,3), and drink C contains (1,2,3), inputting ingredients 1,2 and 3 should return drinks A, B and C. I just started teaching myself database design with MySQL and any help is much appreciated. Sorry if this has been answered elsewhere, I tried but didn't quite know how to search for it.

your example data is too simplistic. Should a recipe with ingredients (1,4) get returned when you put it (1,2,3)? what if you input (1,3) should you return (1,2,3) too? — Stephanie Page, Jul 14 '11 at 20:03
To answer your questions, no. Sorry for the ambiguous language, I believe I got at the question better [here](http://stackoverflow.com/questions/6686665/query-relation-table-against-another-column) — caltangelo, Jul 14 '11 at 22:24

score 3 · Accepted Answer · answered Jul 07 '11 at 22:15

3

TRY

SELECT d.drink_name
FROM tbl_drink d
INNER JOIN tbl_receipe r ON r.drink_id=d.drink_id
INNER JOIN tbl_ingredient i ON i.ingredient_id = r.ingredient_id
WHERE `given ID` IN (i.ingredient_id)

answered Jul 07 '11 at 22:15

xkeshav

53,360
44
177
245

I would, but I'm such a noob I don't have the reputation. – caltangelo Jul 09 '11 at 15:48

score 2 · Answer 2 · answered Jul 07 '11 at 22:10

2

select * from drink d where
  exists (select * from recipe where drink_id = d.drink_id and ingred_id = ?) and
  exists (select * from recipe where drink_id = d.drink_id and ingred_id = ?) and
  [...]

Bind ?'s and add an exists for each ingredient in the set.

answered Jul 07 '11 at 22:10

jspcal

50,847
7
72
76

This answers my question exactly as asked, thank you! Unfortunately I realized that's not exactly what I want. I updated the question accordingly. – caltangelo Jul 08 '11 at 14:37

score -1 · Answer 3 · answered Jul 07 '11 at 22:12

-1

SELECT d.drinks_name
FROM drinks d
JOIN recipes AS r ON (r.drinks_id = d.drinks_id)
JOIN ingredients AS i1 ON (i1.ingredients_id = r.ingredients_id)
JOIN ingredients AS i2 ON (i2.ingredients_id = r.ingredients_id)
...
JOIN ingredients AS iN ON (iN.ingredients_id = r.ingredients_id)
WHERE
    i1.ingredients_name = ${first_ingredient}
    i2.ingredients_name = ${second_ingredient}
    ...
    iN.ingredients_name = ${Nth_ingredient}

answered Jul 07 '11 at 22:12

Jonathan Hall

75,165
16
143
189

This answer looks good, but I couldn't get it to work when I tried it. Perhaps because I'm unfamiliar with the ${xxx} syntax? – caltangelo Jul 08 '11 at 16:49
The ${xxx} syntax is just place holders for your actual ingredients. – Jonathan Hall Jul 08 '11 at 19:20
This requires that you know how many ingredients you want to compare in advance. if n = 3 you can't do 4 ingredients. and you can't do 2 ingredients either unless you put the second on both ${second_ingredient} and ${third_ingredient} – Stephanie Page Jul 14 '11 at 19:55
@Stephanie Page: No, it doesn't require that you know anything in advance. It only requires that you build your SQL statement dynamically. If you _must_ use a statement prepared ahead of time, you can add some `OR ${nth_ingredient} IS NULL` checks, but that will be less efficient. – Jonathan Hall Jul 14 '11 at 22:08

MySQL many to many relationship matching

3 Answers3