Question: I have multiple tables, related by multiple foreign keys as in the following example:

* `Recipes(id_recipe,name,calories,category)` - `id_recipe` as PK.
* `Ingredients(id_ingredient,name,type)` - `id_ingredient` as PK.
* `Contains(id_ingredient,id_recipe,quantity,unit)` - `(id_ingredient,id_recipe)` as PK, and as Foreign Keys for `Recipes(id_recipe)` and `Ingredients(id_ingredient)`.

You can see this relations represented in this image.
![enter image description here](https://i.stack.imgur.com/kYj25.png)

So basically `Contains` is a bridge between `Recipes` and `Ingredients`. 

The query I try to write it's supposed to give as result the names of the recipes whose ingredients type are "bovine" but not "lactic".

My attempt:

`SELECT DISTINCT Recipes.name
FROM Ingredients JOIN Contains USING(id_ingredient) JOIN Recipes USING (id_recipe) 
WHERE Ingredients.type = "bovin" 
AND Ingredients.type <> "lactic";`

The problem is it still shows me recipes that have at least one lactic ingredient. 

I would appreciate any help!

Answer: 
A common solution of this type of question (checking conditions over a set of rows) utilizes aggregate + CASE.

```
SELECT R.Name
FROM Recipes R
INNER JOIN Contains C
 on R.ID_Recipe = C.ID_Recipe
INNER JOIN Ingredients I
 on C.ID_Ingredient = I.ID_Ingredient
GROUP BY R.name
having -- at least one 'lactic' ingredient
  sum(case when type = 'lactic' then 1 else 0 end) = 0
and    -- no 'bovin' ingredient
  sum(case when type = 'bovin' then 1 else 0 end) > 0

```

It's easy to extend to any number of ingredients and any kind of question.

Hijacked the [fiddle](http://sqlfiddle.com/#!9/d4bde1/3) of xQbert
[SQL Fiddle](http://sqlfiddle.com/#!9/d4bde1/1/0)
 In my example burgers and pasta have 'Bovin' and thus show up. So do cookies but cookies also have 'lactic' which is why they get excluded.

```
SELECT R.Name
FROM Recipes R
INNER JOIN Contains C
 on R.ID_Recipe = C.ID_Recipe
INNER JOIN Ingredients I
 on C.ID_Ingredient = I.ID_Ingredient
LEFT JOIN (SELECT R2.ID_Recipe
           FROM Ingredients I2
           INNER JOIN Contains C2 
             on C2.ID_Ingredient = I2.ID_Ingredient
           INNER JOIN Recipes R2               
             on R2.ID_Recipe = C2.ID_Recipe
           WHERE Type = 'lactic'
           GROUP BY R2.ID_Recipe) T3
 on T3.ID_Recipe = R.ID_Recipe
WHERE T3.ID_Recipe is null
 and I.Type = 'Bovin'
GROUP BY R.name

```

There likely is a more elegant way of doing this. I really wanted to CTE this and join it to itself.. but no CTE in mySQL. Likely a way to do this using exists too.... I'm not a big fan of using IN clauses as the performance generally suffers. Exists fastest, Joins 2nd fastest, in slowest (generally speaking)

The inline view (sub query) returns the ID\_recipe of those you don't want to include.

The outer query returns the Name of the recipes with ingredients you want. 

By joining these two together using an outer join we return all recipes and only those with the undesired ingredient. We then limit the results to only those where the recipe ID doesn't exist for the undesired ingredient. (undesired ingredient not found) you'll get only those recipes having all desired ingredients.