Cocktail Ingredients - Query help required

[Yeti]

HI all,

I need some help constructing a query (or a suite of queries!).

I want to work out what Cocktails I can make given the drinks I have
in my drinks cabinet. I have a table of Cocktail names, a table of
ingredients names and a join table that links the cocktails with their
ingredients (breaking the many to many between tblCocktails and
tblIngredients).

I want to be able to select between 2 and 6 drinks (ingredients) that
I have in my drinks cabinet and the query(S) to return to me the most
suitable cocktails I can make. So if I say I have Vodka, Orange Juice
and Champagne it will return a "Russian Brunch" - which uses all
three, a "Screwdriver" - which is Vodka and Orange Juice, a "Bucks
Fizz" - which is Champagne & Orange Juice but not a "Flubber" which is
Vodka, Orange Juice and Sambuca.........

If I can do this I then want to be able to order my results by the
"closest match" i.e. the cocktails at the top of the list will be the
ones that use most of the ingredients, then next will come those that
only use two of the ingredients, and then next those that only use one
of the ingredients I have.....

I've tried various methods but I can't seem to find a solution...

Many thanks in advance.

Yeti

 

[Dale Fye]

Yeti,

You should be able to use this query technique for any recipe you
have. You could take it a step further and indicate how much of each
item you have, then you would have to modify the Sum() portion of the
query to also compare whether the amount of an item on hand is at
least as great as the quantity required for the recipe.

I'll assume you have a temp table (tblAvailable) that contains the
IngredientIDs of those ingredients that you want to use for your
party, and have on hand.

You should be able to link your Cocktails table to your
CocktailIngredients table (on CocktailID) and link the
CocktailIngredients table to your AvailableIngredients table (Left
Join on IngredientsID). Then group by the CocKtail name, Count the
number of require ingredients, and Sum the number of available
ingredients that match with require ingredients. Use this as a
sub-query to get the those coctails for which you have the appropriate
ingredients.

SELECT *, [ReqAvailable]/[Available] as PctAvailUsed
FROM
(SELECT tblCocktails.CocktailName
, tblCocktails.CocktailID
, Count(tblCocktailIngredients.CocktailID) AS Required
, Sum(IIf(IsNull([tblAvailable].[IngredientID]),0,1)) AS
ReqAvailable
, (SELECT Count(*) FROM tblAvailable) as Available
FROM (tblCocktails INNER JOIN tblCocktailIngredients
ON tblCocktails.CocktailID =
tblCocktailIngredients.CocktailID)
LEFT JOIN tblAvailable ON tblCocktailIngredients.IngredientID =
tblAvailable.IngredientID
GROUP BY tblCocktails.CocktailName, tblCocktails.CocktailID)
WHERE ReqAvailable = Required
ORDER BY [ReqAvailable]/[Available] DESC

--
HTH

Dale Fye


HI all,

I need some help constructing a query (or a suite of queries!).

I want to work out what Cocktails I can make given the drinks I have
in my drinks cabinet. I have a table of Cocktail names, a table of
ingredients names and a join table that links the cocktails with their
ingredients (breaking the many to many between tblCocktails and
tblIngredients).

I want to be able to select between 2 and 6 drinks (ingredients) that
I have in my drinks cabinet and the query(S) to return to me the most
suitable cocktails I can make. So if I say I have Vodka, Orange Juice
and Champagne it will return a "Russian Brunch" - which uses all
three, a "Screwdriver" - which is Vodka and Orange Juice, a "Bucks
Fizz" - which is Champagne & Orange Juice but not a "Flubber" which is
Vodka, Orange Juice and Sambuca.........

If I can do this I then want to be able to order my results by the
"closest match" i.e. the cocktails at the top of the list will be the
ones that use most of the ingredients, then next will come those that
only use two of the ingredients, and then next those that only use one
of the ingredients I have.....

I've tried various methods but I can't seem to find a solution...

Many thanks in advance.

Yeti

 

[Yeti]

Dale,

Thanks for the advice. I am sure this would have worked.

What I actually did - after a bit more lateral thinking - was to
obtain the results by first finding the results I didn't want.....I
then did a 'find unmatched' query to select the cocktails I did want
from the main cocktail table.

The SQL is quite long as I wanted to have no other queries
referenced....as I am actually using the SQL in another application !!

Final working SQL statement is attached below...

Thanks for your help anyway.

Yeti

SELECT DISTINCT CocktailsAndIngredients.CocktailID,CocktailsAndIngredients.CocktailName,
Count(CocktailsAndIngredients.IngredientName) AS CountOfIngredientName
FROM [SELECT Cocktail.CocktailID, Cocktail.CocktailName,
Ingredient.IngredientID, Ingredient.IngredientName
FROM Ingredient INNER JOIN (Cocktail INNER JOIN CocktailIngredient ON
Cocktail.CocktailID=CocktailIngredient.CocktailID) ON
Ingredient.IngredientID=CocktailIngredient.IngredientID]. AS
CocktailsAndIngredients LEFT JOIN [SELECT Cocktail.CocktailID,
Cocktail.CocktailName
FROM Ingredient INNER JOIN (Cocktail INNER JOIN CocktailIngredient ON
Cocktail.CocktailID = CocktailIngredient.CocktailID) ON
Ingredient.IngredientID = CocktailIngredient.IngredientID
WHERE (((Ingredient.IngredientName) Not In ("Southern
Comfort","Vodka","Champagne","Orange Juice")))]. AS CantMakeThese ON
CocktailsAndIngredients.CocktailID = CantMakeThese.CocktailID
GROUP BY CocktailsAndIngredients.CocktailID,
CocktailsAndIngredients.CocktailName, CantMakeThese.CocktailID
HAVING (((CantMakeThese.CocktailID) Is Null))
ORDER BY Count(CocktailsAndIngredients.IngredientName) DESC;