Find all recipes with just vegan products

I have been trying to find all recipes with just vegan products but so far I just have this:

SELECT DISTINCT r.*
FROM recipes r INNER JOIN recipe_product rp ON r.id = rp.recipe_id
INNER JOIN products p ON rp.product_id = p.id 
INNER JOIN product_specificdiet ps ON p.id = ps.product_id 
INNER JOIN specificdiets sd ON ps.specific_diet_id = sd.id 
WHERE sd.type = 'VEGAN';

But that query is giving me all the products that are vegan for all the recipes.

No idea how could I resolve it.

Thanks!

Answer

Two rules:

1. A product is vegan, if one of its specific diets is ‘VEGAN’.
2. A recipe is vegan, if it only consists of vegan products.

In other words: A vegan recipe is a recipe for which NOT EXISTS any product that is NOT IN the set of vegan products.

select *
from recipes r
where not exists
(
  select null -- non-vegan ingredient
  from recipe_product rp 
  where rp.recipe_id = r.id
  and rp.product_id not in 
  (
    select ps.product_id -- vegan product
    from product_specificdiet ps
    join specificdiets sd ON ps.specific_diet_id = sd.id 
    where sd.type = 'VEGAN'
  )
);