Query to get list of cocktails given ingredients

[galilee99]

Hi, I'm trying to write a query to get a list of drink recipes which have one or more of the selected ingredients....

so, in a search form I want to be able to choose 1 or more ingredients and have sql return all drinks with that combination of ingredients.

I have 3 tables (only relevant fields shown here):

tDrinks
-drinkid
-drinkname
-drinkdesc

tIngredients (not needed in the query but used elsewhere)
-ingredientid
-ingredientname

tDrinkIngredients
-drinkid
-ingredientid
-qty

so, I can get a list of drinks given one ingredient:

SELECT tDrinks.drinkid,tDrinks.drinkname FROM tDrinks left join tDrinkIngredients on tDrinks.drinkid=tDrinkIngredients.drinkid where tDrinkIngredients.ingredientid=5 

but I am having difficulty understanding how to involve more than one ingredient. Any ideas / suggestions would be much appreciated....

[Atli]

Do you wan't to get a list of drinks that match all the given ingredients or just one of them?

If it's the first one, you could try using a INNER JOIN, and specify all the ingredients you want in the ON clause.
It's only a small modification to the query you posted. (I reformatted it a bit to):

SELECT d.drinkid, d.drinkname 
FROM tDrinks AS d
INNER JOIN tDrinkIngredients AS i
  ON d.drinkid = i.drinkid
  AND i.ingredientid = 1
  AND i.ingredientid = 2
  AND i.ingredientid = 3
  /* etc */
 

If it's the second option, you could use the IN() function.
Like:

SELECT d.drinkid, d.drinkname 
FROM tDrinks AS d
INNER JOIN tDrinkIngredients AS i
  ON d.drinkid = i.drinkid
  AND i.ingredientid IN(1, 2, 3)
 

EDIT: On second though, the first query is obviously not going to work, seeing as I'm checking the same field for several values. See post #7.

[galilee99]

Hi, thanks for the quick response...yes, I want to get drinks only if they have ALL the ingredients searched for....

the second example you gave works great for getting any one ingredient, but I can't seem to get the first query working......here is some more information on the structure of the tables so hopefully someone can help me:

So as you can see in the image, I need to pull any drinks with a combination of any 1 or more ingredient.

Much appreciate any replies...thanks!

[Atli]

What's the problem with the first query?
How is it not working?

[galilee99]

HI, using the following query:

SELECT d.drinkid, d.drinkname  
FROM tDrinks AS d  
INNER JOIN tDrinkIngredients AS i 
ON d.drinkid = i.drinkid 
AND i.ingredientid =63 
AND i.ingredientid =211
 

it returns an empty recordset, when it should return a few records.

[galilee99]

Hi, I thought this was it but it doesn't work correctly either...it's jsut a different way of doing it:

SELECT d.drinkid, d.drinkname 
FROM tDrinks AS d 
INNER JOIN (SELECT i.drinkid
FROM tDrinkIngredients AS i
WHERE i.ingredientid IN (63,211))
 AS i
ON d.drinkid = i.drinkid
 

[Atli]

HI, using the following query:

SELECT d.drinkid, d.drinkname  
FROM tDrinks AS d  
INNER JOIN tDrinkIngredients AS i 
ON d.drinkid = i.drinkid 
AND i.ingredientid =63 
AND i.ingredientid =211
 

it returns an empty recordset, when it should return a few records.

Ahh ok. I see the problem. That query is checking whether the same "i.ingredientid" field has two different values, which is obviously not going to happen.

To make that query work, you would have to JOIN the ingredient table for every ingredient you want checked. Like:

SELECT d.drinkid, d.drinkname  
FROM tDrinks AS d  
INNER JOIN tDrinkIngredients AS i1 
  ON d.drinkid = i1.drinkid 
  AND i1.ingredientid=63 
INNER JOIN tDrinkIngredients AS i2 
  ON d.drinkid = 2i.drinkid 
  AND i2.ingredientid=211
 

It doesn't look pretty, but it's only using integers, who are all presumably keys, so the extra JOINS shouldn't cause much performance loss.

[coolsti]

HI, using the following query:

SELECT d.drinkid, d.drinkname  
FROM tDrinks AS d  
INNER JOIN tDrinkIngredients AS i 
ON d.drinkid = i.drinkid 
AND i.ingredientid =63 
AND i.ingredientid =211
 

it returns an empty recordset, when it should return a few records.

Why would not this work?

SELECT d.drinkid, d.drinkname  
FROM tDrinks AS d  
INNER JOIN tDrinkIngredients AS i 
ON d.drinkid = i.drinkid 
AND i.ingredientid IN (63,211)
 

It is basically your second attempt, but I do not have the WHERE i.ingredientid IN ( ... list ...) within a subquery.

[Atli]

Why would not this work?

He want's a list of drinks that use all the specified ingredients.
That would return all drinks that use any of them.

[coolsti]

Ah I see the problem now!

I don't know if Atli's suggestion works, but I did something like this a while back in another context.

SELECT count(*) as rowcount, d.drinkid, d.drinkname  
FROM tDrinks AS d  
INNER JOIN tDrinkIngredients AS i 
ON d.drinkid = i.drinkid 
AND i.ingredientid IN (63,211)
GROUP BY d.drinkid HAVING rowcount = 2
 

The above might work (haven't tested it for syntax errors and logic errors).

The idea is this: Since your one to many relationship in the join (a drink has many ingredients) will give you multiple rows for the same drinkid if there are more than one ingredient match, you need to do a GROUP BY.

Then you just add this count(*) as rowcount attribute to your query. With the Group by clause, it will give you the count of how many rows have been grouped for each drink.

If only one ingredient matched, rowcount for that drink is 1. If two ingredients matched, then rowcount will be 2.

In your situation, you want all the drinks that match ALL the ingredients that you have selected. So you want only the rows in your results that have rowcount equal to the number of drinks. Hence, you use the HAVING rowcount = 2 here.

To make this flexible for all user selections, you of course need to substitute the IN clause with the list of ingredients that were actually selected, and you need to substitute the value of 2 in the "HAVING rowcount = 2" with the number of ingredients that were selected.

Or did I misunderstand the problem yet once again? :)

[coolsti]

Oops, I just reread my answer and I made a mistake in the explanation:

In your situation, you want all the drinks that match ALL the ingredients that you have selected. So you want only the rows in your results that have rowcount equal to the number of selected ingredients. Hence, you use the HAVING rowcount = 2 here.

Substitute ingredients for drinks!

[galilee99]

Hi, thanks to both Alti and Coolsti. I can use both of those queries in my application. Coolsti, that idea of using rowcounts is a good one as I can do partial ingredient matches on a drink...ie these 6 drinks have 5/5 of the ingredients...these next 20 have 4/5 etc......gives a good way of showing related drinks as well as exact matches.

Thanks!

[coolsti]

Hi, thanks to both Alti and Coolsti. I can use both of those queries in my application. Coolsti, that idea of using rowcounts is a good one as I can do partial ingredient matches on a drink...ie these 6 drinks have 5/5 of the ingredients...these next 20 have 4/5 etc......gives a good way of showing related drinks as well as exact matches.

Thanks!

You are most welcome! I am just giving back what was given to me. I had a similar situation: I allowed a user to select various inspection results criteria from a large list, and wanted to select from the database for view only those items for which there was a match with all of the selected criteria, be it only one or two or five, etc.

I had no idea how to do it, and I posted a question on another forum long ago, and someone was nice enough to point out the trick with the HAVING clause to me.

And yes, it is versatile, because you can have the criterion after the HAVING be an equality or a range.