sql - How to return a row only if multiple clauses are met -

i working on application dispense liquids. here organization of 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 ingredientinstance table), there 12 canisters. trying write sql command gather drinks have of required ingredients in canisters.

so far, have.

select distinct drink.drink_name drink, ingredientinstance, canister (((ingredientinstance.drink_id)=[drink].[drink_id]) , ((ingredientinstance.ingredient_id)  in (select ingredient_id canister))); 

however, returning drinks have single ingredient available. looking way ensure every associated ingredient in ingredientinstance, in canister.

for example, let's drink1 requires ingredient1 , ingredient2. want appear in result if both of ingredient ids present in canisters, not if 1 or 0 ingredients in canister.

i'm sure it's obvious, can't think of how this.

this example of set-within-sets query. advocate using aggregation having clause this:

select drink.drinkname ingredientinstance ii join      drink d      on ii.drinkid = d.drinkid left join      canister c      on ii.ingredientid = c.ingredientid group drink.drinkname having sum(iif(c.ingredientid null, 1, 0)) = 0; 

this joining ingredientinstance , drink drink name. doing left join canister table. keeps ingredients in drinks along matching ingredients (if any) in canisters. if ingredient missing canister, has null ingredientid.

the group by looks @ ingredients drink. final having clause counts number of missing ingredients, , returns drinks no missing ingredients.