469,126 Members | 1,291 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

Query to get list of cocktails given ingredients

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:
Expand|Select|Wrap|Line Numbers
  1. 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....
Sep 15 '08 #1
12 3301
Atli
5,058 Expert 4TB
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):
Expand|Select|Wrap|Line Numbers
  1. SELECT d.drinkid, d.drinkname 
  2. FROM tDrinks AS d
  3. INNER JOIN tDrinkIngredients AS i
  4.   ON d.drinkid = i.drinkid
  5.   AND i.ingredientid = 1
  6.   AND i.ingredientid = 2
  7.   AND i.ingredientid = 3
  8.   /* etc */
  9.  
If it's the second option, you could use the IN() function.
Like:
Expand|Select|Wrap|Line Numbers
  1. SELECT d.drinkid, d.drinkname 
  2. FROM tDrinks AS d
  3. INNER JOIN tDrinkIngredients AS i
  4.   ON d.drinkid = i.drinkid
  5.   AND i.ingredientid IN(1, 2, 3)
  6.  
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.
Sep 15 '08 #2
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!
Sep 15 '08 #3
Atli
5,058 Expert 4TB
What's the problem with the first query?
How is it not working?
Sep 16 '08 #4
HI, using the following query:


Expand|Select|Wrap|Line Numbers
  1. SELECT d.drinkid, d.drinkname  
  2. FROM tDrinks AS d  
  3. INNER JOIN tDrinkIngredients AS i 
  4. ON d.drinkid = i.drinkid 
  5. AND i.ingredientid =63 
  6. AND i.ingredientid =211
  7.  
it returns an empty recordset, when it should return a few records.
Sep 16 '08 #5
Hi, I thought this was it but it doesn't work correctly either...it's jsut a different way of doing it:

Expand|Select|Wrap|Line Numbers
  1. SELECT d.drinkid, d.drinkname 
  2. FROM tDrinks AS d 
  3. INNER JOIN (SELECT i.drinkid
  4. FROM tDrinkIngredients AS i
  5. WHERE i.ingredientid IN (63,211))
  6.  AS i
  7. ON d.drinkid = i.drinkid
  8.  
Sep 16 '08 #6
Atli
5,058 Expert 4TB
HI, using the following query:


Expand|Select|Wrap|Line Numbers
  1. SELECT d.drinkid, d.drinkname  
  2. FROM tDrinks AS d  
  3. INNER JOIN tDrinkIngredients AS i 
  4. ON d.drinkid = i.drinkid 
  5. AND i.ingredientid =63 
  6. AND i.ingredientid =211
  7.  
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT d.drinkid, d.drinkname  
  2. FROM tDrinks AS d  
  3. INNER JOIN tDrinkIngredients AS i1 
  4.   ON d.drinkid = i1.drinkid 
  5.   AND i1.ingredientid=63 
  6. INNER JOIN tDrinkIngredients AS i2 
  7.   ON d.drinkid = 2i.drinkid 
  8.   AND i2.ingredientid=211
  9.  
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.
Sep 16 '08 #7
coolsti
310 100+
HI, using the following query:


Expand|Select|Wrap|Line Numbers
  1. SELECT d.drinkid, d.drinkname  
  2. FROM tDrinks AS d  
  3. INNER JOIN tDrinkIngredients AS i 
  4. ON d.drinkid = i.drinkid 
  5. AND i.ingredientid =63 
  6. AND i.ingredientid =211
  7.  
it returns an empty recordset, when it should return a few records.
Why would not this work?

Expand|Select|Wrap|Line Numbers
  1. SELECT d.drinkid, d.drinkname  
  2. FROM tDrinks AS d  
  3. INNER JOIN tDrinkIngredients AS i 
  4. ON d.drinkid = i.drinkid 
  5. AND i.ingredientid IN (63,211)
  6.  
It is basically your second attempt, but I do not have the WHERE i.ingredientid IN ( ... list ...) within a subquery.
Sep 16 '08 #8
Atli
5,058 Expert 4TB
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.
Sep 16 '08 #9
coolsti
310 100+
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT count(*) as rowcount, d.drinkid, d.drinkname  
  2. FROM tDrinks AS d  
  3. INNER JOIN tDrinkIngredients AS i 
  4. ON d.drinkid = i.drinkid 
  5. AND i.ingredientid IN (63,211)
  6. GROUP BY d.drinkid HAVING rowcount = 2
  7.  
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? :)
Sep 16 '08 #10
coolsti
310 100+
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!
Sep 16 '08 #11
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!
Sep 17 '08 #12
coolsti
310 100+
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.
Sep 17 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Muhd | last post: by
33 posts views Thread by Jim Cobban | last post: by
reply views Thread by Paul | last post: by
2 posts views Thread by shanfitzpatrick | last post: by
4 posts views Thread by =?Utf-8?B?amF5dg==?= | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.