Recipe Database, Ingredient Search

Question

I have the following 3 tables in my database, and I have some problems querying them for the results I want. I am trying to find recipes for ingredients.

SQL script script below: fiddle

Here are my tables: Ingredients

+---------------+---------+ | ingredient_id | name | +---------------+---------+ | 1 | tomato | | 2 | onion | | 3 | rice | | 4 | chicken | | 5 | beef | | 6 | noodles | | 7 | salt | +---------------+---------+ 

Recipes

 +-----------+------------------+ | recipe_id | name | +-----------+------------------+ | 1 | tomato goodness | | 2 | meat deluxe | | 3 | chicken surprise | +-----------+------------------+ 

Ingredient_Index

 +-----------+---------------+ | recipe_id | ingredient_id | +-----------+---------------+ | 1 | 1 | | 1 | 5 | | 1 | 7 | | 2 | 5 | | 2 | 6 | | 2 | 7 | | 3 | 4 | | 3 | 3 | | 3 | 7 | +-----------+---------------+ 

a search query for only one ingredient works fine and outputs this:

 mysql> select r.recipe_id, r.name -> from recipes r -> inner join ingredient_index -> on i.recipe_id = r.recipe_id -> where -> i.ingredient_id = 7; +-----------+------------------+ | recipe_id | name | +-----------+------------------+ | 1 | tomato goodness | | 2 | meat deluxe | | 3 | chicken surprise | +-----------+------------------+ 

But when using or for several ingredients we get this

 mysql> select r.name -> from recipes r -> inner join ingredient_index i -> on i.recipe_id = r.recipe_id -> where i.ingredient_id = 7 or i.ingredient_id = 5; +------------------+ | name | +------------------+ | tomato goodness | | tomato goodness | | meat deluxe | | meat deluxe | | chicken surprise | +------------------+ 

5 lines per set (0.00 sec)

and using "and" results without anything

  mysql> select r.name -> from recipes r -> inner join ingredient_index i -> on i.recipe_id = r.recipe_id -> where i.ingredient_id = 7 and i.ingredient_id = 5; Empty set (0.00 sec) 

Any help would be greatly appreciated!

Answer 1

Since a recipe can use several ingredients, and you are looking for recipes that use one or more of the specified ingredients, you should use the DISTINCT keyword to prevent duplicate results when a recipe uses more than one ingredient from the list indicated. Alternatively, you can use the IN clause to filter multiple ingredient identifiers.

 select DISTINCT r.name from recipes r inner join ingredient_index i on i.recipe_id = r.recipe_id where i.ingredient_id IN (7, 5); 

Alternatively, if you are looking for recipes that use all the ingredients listed, you can group the results by the name of the recipe and check if the number of entries matches the number of ingredients in your list.

 select r.name from recipes r inner join ingredient_index i on i.recipe_id = r.recipe_id where i.ingredient_id IN (7, 5) GROUP BY r.name HAVING COUNT(*) = 2 

This assumes that there will be no duplicate records with the same (recipe_id, componentent_id) tuple (better provided with UNIQUE constraint).

Answer 2

Below fiddle

this request:

 select distinct recipe.name from recipes recipe, ingredient_index i where i.ingredient_id = 7 or i.ingredient_id = 5; 

gives this result:

 NAME tomato goodness meat deluxe chicken surprise