CODEWITHSUNDEEP
mysqlsqldatabasefilter
Deividas
Deividas

Reputation: 79

Recipe Database, search by ingredients

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: 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 |
+-----------+---------------+

What I would like to achieve, Is to filter all recipes I can make using specified ingredients. And here comes the problem:

This query:

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

Gives me false results, because I don't have enough ingredients to make any of recipes, yet still I get a result that I can make all of them. That happens because recipe_id is duplicating in Ingredient_Index table.

Any help would me much appreciated.

Upvotes: 2

Views: 1163

Answers (3)

George Menoutis
George Menoutis

Reputation: 7240

As jarlh said, check no ingredient missing:

select DISTINCT r.name
from recipes r
where not exists (
select 1 from ingredient_index i where r.recipe_id=i.recipe_id and i.ingredient_id not in (2,5,7)
)

Upvotes: 2

jarlh
jarlh

Reputation: 44766

Another way:

select r.name
from recipes r
   join ingredient_index i on i.recipe_id = r.recipe_id
where i.ingredient_id IN (2, 7, 5)
group by r.name
having count(i.ingredient_id) = 3

Upvotes: 0

Cynical
Cynical

Reputation: 9568

Mine follows the other suggestion jarlh gave, and checks whether all ingredients are available:

select distinct a.name
from (select r.name, count(*) as ing_available
      from 
          recipes r
          inner join ingredient_index i
          on i.recipe_id = r.recipe_id
      where i.ingredient_id IN (1, 7, 5)
      group by r.recipe_id) 
as a join 
      (select r.name, count(*) as ing_required
      from 
          recipes r
          inner join ingredient_index i
          on i.recipe_id = r.recipe_id
      group by r.recipe_id)
as p 
on p.ing_required = a.ing_available

Upvotes: 0

Related Questions