CODEWITHSUNDEEP
sqlms-access
Tadgh
Tadgh

Reputation: 2049

How to return a row only if multiple clauses are met

I am working on an application to dispense liquids. here is the organization of the DB

CANISTER:

INGREDIENT:

DRINK:

INGREDIENTINSTANCE:

Each drink has multiple ingredients(held in the ingredientInstance table), but there are only 12 canisters. I am trying to write an SQL command that will gather all drinks that have ALL of their required ingredients currently in canisters.

So far, this is what I have. 

SELECT DISTINCT Drink.drink_name
FROM Drink, ingredientInstance, Canister
WHERE (((ingredientInstance.drink_id)=[Drink].[drink_id])
AND ((ingredientInstance.ingredient_id) 
IN (select ingredient_id FROM Canister)));

However, this is returning all drinks that have even a single ingredient available. What I am looking for is a way to ensure that for every associated ingredient in ingredientInstance, it is currently in a canister.

For example, let's say that drink1 requires ingredient1 and ingredient2. I want it to appear in the result if both of those ingredient IDs are present in the Canisters, but not if only one or zero ingredients are in the canister.

I'm sure it's something obvious, but I can't think of how to do this.

Upvotes: 3

Views: 90

Answers (2)

vinodpthmn
vinodpthmn

Reputation: 1062

select d.* from drink d inner join
(select i.drink_id, sum(case when c.canister_id is null 1 else 0 end) as fullcanister
from ingredientinstance i left join canister c on c.ingredient_id=i.ingredient_id 
group by i.drink_id) as id on id.drink_id=d.drink_id and id.fullcanister=0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

This is an example of a set-within-sets query. I advocate using aggregation with a having clause for this:

select drink.drinkname
from IngredientInstance ii join
     Drink d
     on ii.DrinkId = d.Drinkid left join
     Canister c
     on ii.IngredientId = c.INgredientId
group by drink.drinkname
having sum(iif(c.IngredientId is null, 1, 0)) = 0;

This is joining IngredientInstance and Drink just to get the drink name. It is then doing a left join to the Canister table. This keeps all the ingredients in the drinks along with matching ingredients (if any) in the canisters. If an ingredient is missing from Canister, then it has a NULL ingredientId.

The group by looks at the ingredients by drink. The final having clause counts the number of missing ingredients, and only returns drinks with no missing ingredients.

Upvotes: 2

Related Questions