How to return a row only if multiple clauses are met

Question

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

CANISTER:

canister_id{PK}
ingredient_id{FK}

INGREDIENT:

ingredient_id{PK}
ingredient_name

DRINK:

drink_id{PK}
drink_name

INGREDIENTINSTANCE:

instance_id{PK}
drink_id{FK}
ingredient_id{FK}
amount

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.

What you want is called relational division. Take a look at this question: [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) — ypercubeᵀᴹ, May 08 '13 at 18:41

score 2 · Accepted Answer · answered May 08 '13 at 18:46

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.

vinodpthmn · Answer 2 · 2013-05-09T05:29:36.113

1

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

edited May 09 '13 at 05:29

answered May 08 '13 at 18:47

vinodpthmn

1,062
14
28

1

. . This doesn't work. All the `where` clause does is turn the `left outer join` into an `inner join`. In other words, it will returns drinks that have *any* ingredient in the canister table, as opposed to all ingredients in the table. – Gordon Linoff May 08 '13 at 19:31

How to return a row only if multiple clauses are met

2 Answers2

Linked