[SOLVED] MySQL many to many relationship matching

MySQL many to many relationship matching

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.

Solution

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)