How to conditionally select rows based on other rows

user_3166194 0
Viewed 54

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
)
relational-division subquery ms-access count sql
2 Answers

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.
user_11440350

Consider an aggregate query with HAVING condition to check recipe counts against available ingredients count. The conditional aggregation is multiplied by -1 due to Access value of True boolean at -1.

SELECT r.Recipe 
FROM tblRecipes r
INNER JOIN Ingredient i 
  r.recipe = i.recipe
GROUP BY r.Recipe
HAVING COUNT(*) = SUM(i.available = 'true') * -1
user_14224510
Related