Find Items Where All Parts Are In Stock

Question

Hopefully fairly easy query, but my mind is just not working this afternoon. I've got the following tables:

tblCocktail
CockTailID  CocktailName
1           Alexander

tblCocktailIngredient
CocktailID  IngredientID   Amount   UnitID
1           1              3        1
1           2              3        1
1           3              3        1

tblIngredient
IngredientID  IngredientName   OnHandAmount   OnHandUnitID
1             Cognac           .75            2
2             Creme de Cacao   .9             2
3             Cream            .5             2

tblUnitConversion
FromUnitID    ToUnitID   Factor 
1             2          100

What I'm trying to do is find a list of cocktails I can make since I have enough on hand of all of the ingredients. I have the following query:

SELECT tblCocktail.CocktailName
FROM tblCocktail INNER JOIN (tblIngredient INNER JOIN (tblCocktailIngredient INNER JOIN  tblUnitConversion ON tblCocktailIngredient.UnitID = tblUnitConversion.ToUnitID) ON (tblIngredient.IngredientID = tblCocktailIngredient.IngredientID) AND (tblIngredient.OnHandUnit = tblUnitConversion.FromUnitID)) ON tblCocktail.CocktailID = tblCocktailIngredient.CocktailID
WHERE ((([tblCocktailIngredient].[Amount]*[Factor])<[tblIngredient].[OnHandAmount]));

This will give a list of all Cocktails and related ingredients where the onHand is greater than the Amount, however, I only want to list Cocktails where ALL of the ingredients have enough quantity on hand.

Answer 1

SELECT tblCocktail.CocktailName
FROM tblCocktail
WHERE   (   SELECT  COUNT(*) 
            FROM    tblCocktailIngredient 
            WHERE tblCocktailIngredient.CocktailID = tblCocktail.CockTailID) 
        =
        (   SELECT  COUNT(*)
            FROM    tblIngredient 
                    INNER JOIN (tblCocktailIngredient
                                INNER JOIN tblUnitConversion 
                                    ON tblCocktailIngredient.UnitID = tblUnitConversion.FromUnitID)
                        ON (tblIngredient.IngredientID = tblCocktailIngredient.IngredientID)
                            AND (tblIngredient.OnHandUnitId = tblUnitConversion.ToUnitID) 


            WHERE   [tblCocktailIngredient].[Amount] <= [tblIngredient].[OnHandAmount]*[Factor]
                    AND tblCocktailIngredient.CocktailID = tblCocktail.CocktailID )