这是一个 set-within-sets 查询示例.我提倡使用带有 have 子句的聚合:

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;

这是加入 IngredientInstance 和 Drink 只是为了获取饮料名称.然后对 Canister 表执行 left join.这将保留饮料中的所有成分以及罐中的匹配成分(如果有).如果 Canister 中缺少一种成分，则它的 ingredientId 为 NULL.

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.

group by 按饮料查看成分.最后的 have 子句计算缺失成分的数量，并且只返回没有缺失成分的饮料.

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.