CODEWITHSUNDEEP
mysqljoinrelational-databaseassociative-table
caltangelo
caltangelo

Reputation: 274

MySQL many to many relationship matching

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

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.

Upvotes: 3

Views: 540

Answers (3)

xkeshav
xkeshav

Reputation: 54016

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)

Upvotes: 3

Jonathan Hall
Jonathan Hall

Reputation: 79576

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}

Upvotes: -1

jspcal
jspcal

Reputation: 51904

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.

Upvotes: 2

Related Questions