MySQL get values where multiple IDs match

Question

I’m trying to create a recipe search where one can tag multiple ingredients and then you get recipe results based on different search types.

Say the user searches for ingredients, the system will then first do verification of the ingredients themselves and get the IDS of the searched ingredients. Thus the ingredientIDs are then passed to the next query, which depends on the search type a user used to search. The current search type would need to match recipes that exactly match the IDs of the searched ingredients.

Below is the table where ingredients are related to my recipes.

**recipeIngredientList
recipeID | ingredientID
1        | 1
1        | 2
1        | 3
2        | 4
2        | 2
2        | 6
3        | 1
3        | 7
3        | 2

Below is the recipes table.

**recipes   
recipeID | recipeName
1        | Mac & Cheese
2        | Beef Soup
3        | Cheese Toasty

So say the user searches for cheese and butter and these are then ingredientID 1 = cheese and 2 = butter, then those two ingredientIDs should be used to get recipes from the recipe table that use both those ingredients. Thus my result should only give me the recipes “Mac & cheese” and “Cheese Toasty”.

However I’ve realised I’m not even sure what the best way would be to get the results or how the MySQL statement should look like. Should I be joining tables? Or do a multi select? And if so how?

Thanks in advance for helping.

John Woo · Accepted Answer · 2018-01-22 09:18:25Z

1

You can do it this way. First, join the two tables and find its ingredients. Then count the total number of rows that match to total number of ingredients in your where clause.

SELECT  a.RecipeID, a.recipeName
FROM    Recipes a
        INNER JOIN recipeIngredientList b
            ON a.recipeID = b.recipeID
WHERE   b.ingredientID IN (1, 2) -- ingredients
GROUP   BY a.RecipeID, a.recipeName
HAVING  COUNT(*) = 2 -- number of ingredients

Here's a Demo.

answered Jan 22, 2018 at 9:18

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Collectives™ on Stack Overflow

MySQL get values where multiple IDs match

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related