How to conditionally select rows based on other rows

Question

I've got two tables with a simple 1:n relation. One table contains ingredients and their availability. The other contains recipes and their respective ingredients.

Table recipes

RecipeA   | IngredientA
RecipeA   | IngredientB
RecipeB   | IngredientA
RecipeB   | IngredientC

and table ingredients

IngredientA | true
IngredientB | true
IngredientC | false

Querying all rows of table recipes where the ingredient is available is trivial. But how do I select only recipes where ALL ingredients are available? The result should be

RecipeA

I assume it can be done with the ALL operator but I didn't succeed yet. I tried without success

SELECT Recipe 
FROM tblRecipes  
WHERE Ingredient = ALL ( 
  SELECT Ingredient  
  FROM tblIngredients  
  WHERE Available
)

Answer 1

One method uses aggregation:

select r.recipe
from recipes as r inner join
     ingredients as i
     on r.ingredient = i.ingredient
group by r.recipe
having sum(iif(i.available = "false", 1, 0)) = 0;

The sum() is counting the number of non-available ingredients for a given recipe. The = 0 means that none are "not available".

By the way, the data model is poorly named. You should have three tables:

• Recipes with one row per recipe.
• Ingredients with one row per ingredient.
• RecipeIngredients with one row per ingredient in each recipe.