CODEWITHSUNDEEP
mysqlsqldatabase
ThomasMalloch13
ThomasMalloch13

Reputation: 113

Recipe Database, search by ingredient

I have the following 3 tables in my database, and am having some trouble querying them for the results I want. I'm trying to search for recipes by ingredients.

SQL Fiddle of the schema 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 query to search 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 multiple 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 rows in set (0.00 sec)

and using "and" results with nothing

    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 much appreciated!

Upvotes: 5

Views: 5121

Answers (2)

Woot4Moo
Woot4Moo

Reputation: 24316

The following fiddle

this query: 

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

yields this result set: 

NAME  
tomato goodness  
meat deluxe  
chicken surprise

Upvotes: 0

Vikdor
Vikdor

Reputation: 24124

Since a recipe can use multiple ingredients and you are looking for recipes that use one or more of the ingredients specified, you should use the DISTINCT keyword to prevent duplicate results where a recipe is using more than one ingredient from the list specified. Also, you can use IN clause to filter on multiple ingredient IDs.

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 are using all the ingredients specified in the list, then you can group the results by recipe name and check if the count of records is same as 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 is assuming that there won't be duplicate records with same (recipe_id, ingredient_id) tuple (better ensured with a UNIQUE constraint).

Upvotes: 9

Related Questions