MySQL query recipe with all incredients available

Question

Assuming I have 4 MySQL tables:

recipe: id, name
incredient: id, name
recipe_incredient: id, recipe_id, incredient_id
available: id, incredient_id

I want to have a query, which returns all recipes + incredient which there are incredients for.

Example:

I have Toast (= bread + ham) and Soup (= ham + chicken). I have bread and ham. Therefore I want to get Toast, but no Soup.

recipes:
1, Toast
2, Soup

incredients:
1, bread
2, ham
3, chicken

recipe_incredient:
1, 1, 1
2, 1, 2
3, 2, 2
3, 2, 3

available:
1, 1
2, 2


Result should be:
Toast, bread
Toast, ham

Not really =) This is a simplified version of my problem. I am stuck on the exclusion part. — Klaus, Oct 21 '15 at 22:47

score 0 · Accepted Answer · answered Oct 21 '15 at 23:02

The exclusion part is a double NOT EXISTS clause: Find recipes where "not exists" an ingredient that is "not available".

SQL Fiddle

select r.name as recipe, i.name as incredient
  from recipe r
  join recipe_incredient ri on ri.recipe_id = r.id
  join incredient i on i.id = ri.incredient_id
 where not exists (
          select *
            from recipe_incredient ri2
           where ri2.recipe_id = r.id
             and not exists (
                    select *
                      from available a
                     where a.incredient_id = ri2.incredient_id
                 )
       )

Can also be done using NOT IN.

select r.name as recipe, i.name as incredient
  from recipe r
  join recipe_incredient ri on ri.recipe_id = r.id
  join incredient i on i.id = ri.incredient_id
 where r.id not in (
          select ri2.recipe_id
            from recipe_incredient ri2
           where ri2.incredient_id not in (
                    select a.incredient_id
                      from available a
                 )
       )

Whichever is best depends on available indexes, the SQL Optimizer, and your personal preference.

Is there a reason you use join instead of: `FROM recipe r, recipe_incredient, incredient WHERE recipe_incredient.recipe_id = r.id AND recipe_incredient.incredient_id = incredient.id` — Klaus, Oct 22 '15 at 09:02
@Klaus See [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/q/1018822/5221149). — Andreas, Oct 22 '15 at 14:37

MySQL query recipe with all incredients available

1 Answers1