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
You can use `NOT EXISTS` for this.

Try this:

```
SELECT DISTINCT Recipes.`name`
FROM Recipes JOIN Contains AS C1 USING (id_recipe) JOIN Ingredients USING(id_ingredient)
WHERE Ingredients.type = "bovin"
  AND NOT EXISTS (
    SELECT 1
    FROM Contains AS C2 JOIN Ingredients USING(id_ingredient)
    WHERE C1.id_recipe = C2.id_recipe
      AND Ingredients.type = "lactic"
)

```