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
)

Gordon Linoff · Accepted Answer · 2020-09-06T02:16:17.407

2

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.

edited Sep 06 '20 at 02:16

answered Sep 05 '20 at 11:32

Gordon Linoff

1,242,037
58
646
786

1

Thank you, that's a nice approach. I assume you mean `on r.ingredient = i.ingredient` – Nikno Sep 05 '20 at 11:50
1

Note: MS Access SQL requires `join` to be specific: `inner join`, `left join`, etc. Otherwise it will throw an error. – Parfait Sep 05 '20 at 13:16
@Parfait It also requires `i.available = false` with no quotation marks, or alternatively `i.available = 0` – Nikno Sep 05 '20 at 16:03
@GordonLinoff I actually have 3 tables as you suggested, I thought it would complicate the question, though. – Nikno Sep 05 '20 at 16:08

score 1 · Answer 2 · answered Sep 05 '20 at 13:28

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

How to conditionally select rows based on other rows

2 Answers2