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: 
This is the general form of the kind of query you need:

```
SELECT *
FROM tableA
WHERE tableA.ID NOT IN (
   SELECT table_ID
   FROM ...
)
;

```

-- EXAMPLE BELOW --

The subquery gives the id values of all recipes that the "lactic" ingredient is used in, the outer query says "give me all the recipes not in that list".

```
SELECT DISTINCT Recipes.name
FROM Recipes
WHERE id_recipe IN (
   SELECT DISTINCT id_recipe 
   FROM `Ingredients` AS `i`
      INNER JOIN `Contains` AS `c` USING (id_ingredient)
   WHERE `i`.`type` = "lactic"
)
;

```

---

Alternatively, using your original query:
You could've changed the second join to a `LEFT JOIN`, changed it's `USING` to an `ON` & included `AND type = "lactic"` there instead, and ended the query with `HAVING Ingredients.type IS NULL` (or WHERE, I just prefer HAVING for "final result" filtering). This would tell you which items could not be joined to the "lactic" ingredient.
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"
)

```