Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How would I do this? 2

Status
Not open for further replies.
youradds

youradds

Programmer
Jun 27, 2001
817
GB
Hi,

Sorry for the non very descriptive title - wasn't sure what to call this thread =)

Basically, I have a table:

Code: 
IngredientSearch
   recipe_id
   ingredient

Now, what I wanna do - is a search based on a + and -

For example:

+sugar
+cream
-nuts

This would need to query that table (which would have 1 entry per recipe, per ingredient) ... and then once I've got that, I can simply parse it into a template

I've done similar stuff to this before - but not quite the same, so I'm at a bit of a loss as to how to approach this.

Any suggestions are much appreciated :)

TIA

Andy
 
Sort by date Sort by votes
Code: 
SELECT id
  FROM IngredientSearch
 WHERE ingredient IN ('sugar','cream','nuts')
GROUP
   BY id
HAVING COUNT(CASE WHEN ingredient IN ('sugar','cream')
                  THEN 1 ELSE 0 END) = 2
   AND COUNT(CASE WHEN ingredient = 'nuts'
                  THEN 1 ELSE 0 END) = 0


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
Wow, thats a bit more complicated than I was expecting <G>

Will give it a go - thanks for taking the time to help - much appreciated :)

Cheers

Andy
 
Upvote 0 Downvote
Hi,

Just gave it a go - but didn't get any results :(


Code: 
SELECT link_id
  FROM glinks_SearchIngredients 
 WHERE ingredient IN ('something','something else','water')
GROUP
   BY link_id
HAVING COUNT(CASE WHEN ingredient IN ('something','something else')
                  THEN 1 ELSE 0 END) = 2
   AND COUNT(CASE WHEN ingredient = 'water'
                  THEN 1 ELSE 0 END) = 0

Here is the test data I have:

Code: 
link_id  	ingredient
2 	something
2 	something else
2 	sugar
3 	sugar
3 	water
3 	flour

Any ideas what I'm doing wrong?

TIA

Andy
 
Upvote 0 Downvote
BTW, the above - I was trying to do:

+something
+something else
-water

TIA

Andy
 
Upvote 0 Downvote
OMG i'm sorry, i shouldn't write SQL before my first coffee of the day

please replace [red]THEN 1 ELSE 0[/red] with [blue]THEN 'ok' ELSE NULL[/blue]

thanks

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
haha np - you legend, works a charm now <G>

Thanks a ton! (star coming your way :))

BTW, still gotta get round to buying your book ;)

Cheers

Andy
 
Upvote 0 Downvote
Hi,

Just coming back to this now :)

The first bit works fine (where you want to include ingredients, and NOt include others.

I've also modified it so you can search using the same format for records that DO have the ingredients passed in.

Code: 
SELECT link_id
  FROM SearchIngredients 
 WHERE ingredient IN ('onion','tomatoes')
GROUP
   BY link_id
HAVING COUNT(CASE WHEN ingredient IN ('onion','tomatoes')
                  THEN 'ok' ELSE NULL END) = 2


However, trying to do it so that records are grabbed when it DOESNT match any of the passsed in paramaters doesn't seem to work:

Code: 
SELECT link_id
  FROM SearchIngredients 
 WHERE ingredient IN ('garlic')
GROUP
   BY link_id
HAVING COUNT(CASE WHEN ingredient IN ('garlic')
                  THEN 'ok' ELSE NULL END) = 0

(should give a result, as there are tons of entries which don't have "garlic" as an ingredient in that table)

FYI, people can do any of the following searches:

1) Includes all of these ingredients
2) DOESNT include any of these ingredients
3) Includes all of the ingredients, and DOESNT include any of the other ingredients

Am I doing something wrong?

TIA!

Andy
 
Upvote 0 Downvote
Am I doing something wrong?
Click to expand...
yes :)


the FROM and WHERE clauses extract certain rows from the table, and the HAVING clause performs the final test to determine which link_ids qualify

however, for the "doesn't include garlic" problem, if garlic is the only thing you pull out, then there won't be any other rows to count

see what i mean?

you can do it without the WHERE clause, by pulling everything out, then apply the "no garlic" HAVING clause and it'll work

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
LMAO - simple when you know how <G>

Thanks again!

Andy
 
Upvote 0 Downvote
Hi,

Sorry to keep on about this ;)

Is there any way we can do it with LIKE in those queries?

For example, if we pass in "egg" currently, but the index has "eggs", then we get no match.

Is this possible?

TIA!

Andy
 
Upvote 0 Downvote
Hi,

Thanks... I'm assuming the first WHERE would be something like this?

Code: 
  FROM SearchIngredients 
 WHERE ingredient LIKE '%water%' AND 
ingredient LIKE '%sugar%'

Not sure how to conver the other IN bits though?

Here is the current syntax I'm using:

Code: 
SELECT link_id
  FROM SearchIngredients 
WHERE ingredient IN ('something','whatever')
GROUP
   BY link_id
HAVING COUNT(CASE WHEN ingredient IN ('something','whatever')
                  THEN 'ok' ELSE NULL END) = 2|;
   AND COUNT(CASE WHEN ingredient IN ('dont','want','these')
                  THEN 'ok' ELSE NULL END) = 0

TIA!

Andy
 
Upvote 0 Downvote
Not sure how to conver the other IN bits though?
Click to expand...
almost the same way :)


IN(list,of,values) is equivalent to a series of ORs, so you want OR instead of AND

Code: 
SELECT link_id
  FROM SearchIngredients
 WHERE ingredient LIKE '%something%'
    OR ingredient LIKE '%whatever%'
GROUP
   BY link_id
HAVING COUNT(CASE WHEN ingredient LIKE '%something%'
                    OR ingredient LIKE '%whatever%'
                  THEN 'ok' ELSE NULL END) = 2
   AND COUNT(CASE WHEN ingredient LIKE '%dont%'
                    OR ingredient LIKE '%want%'
                    OR ingredient LIKE '%these%'
                  THEN 'ok' ELSE NULL END) = 0
however, i should point out that the second condition in the HAVING clause is guaranteed to be 0, since none of those rows were passed through to the GROUP BY from the WHRE clause

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
Hi,

Your a star - will have a play around with that in a bit. Sure it will work though :)

Thanks again! (another star coming your way ;))

Andy
 
Upvote 0 Downvote
Hi,

Just trying this, and it seems to be acting a little weird =)

Code: 
SELECT link_id FROM SearchIngredients 
   WHERE ingredient LIKE '%egg%' 
   GROUP BY link_id 
    HAVING COUNT(CASE WHEN ingredient LIKE '%egg%' THEN 'ok' ELSE NULL END) = 2 
   AND 
    COUNT(CASE WHEN ingredient OR '%coffee%' THEN 'ok' ELSE NULL END) = 0 
LIMIT 0,25

It seems to be finding records that have "coffee" in (even though it shouldn't do)

Is there anything I can do about that?

TIA

Andy
 
Upvote 0 Downvote
Hi,

Oops, that was a bit of a basic boo-boo ;) I've fixed that up now (should be a LIKE instead of OR)

However, it still seems to be getting results for stuff that shouldn't be

Code: 
SELECT link_id FROM SearchIngredients 
WHERE ingredient LIKE '%egg%' 
GROUP BY link_id 
HAVING 
   COUNT(CASE WHEN ingredient LIKE '%egg%' THEN 'ok' ELSE NULL END) = 2 
   AND 
   COUNT(CASE WHEN ingredient LIKE '%coffee%' THEN 'ok' ELSE NULL END) = 0 
LIMIT 0,25

Here is an example from listing 157, which is coming up at the top of the results:

Code: 
+---------+--------------+
| link_id | ingredient   |
+---------+--------------+
| 157     | egg          |
| 157     | whites       |
| 157     | caster       |
| 157     | sugar        |
| 157     | hazelnut     |
| 157     | meal         |
| 157     | plain        |
| 157     | flour        |
| 157     | dark         |
| 157     | chocolate    |
| 157     | cocoa        |
| 157     | powder       |
| 157     | cream        |
| 157     | mars         |
| 157     | bars         |
| 157     | and          |
| 157     | strawberries |
| 157     | decorate     |
| 157     | thickened    |
| 157     | cream        |
| 157     | mars         |
| 157     | bars         |
| 157     | coffee       |
| 157     | flavoured    |
| 157     | liqueur      |
| 157     | eggs         |
| 157     | gelatine     |
| 157     | water        |
| 157     | caster       |
| 157     | sugar        |
+---------+--------------+

As you can see from the above query, I'm trying to do

HAS "egg"
DOESNT HAVE "coffee"

Any suggestions? Thanks again for your time - much appreciated :)

Cheers

Andy
 
Upvote 0 Downvote
this is exactly the same problem you had earlier (above), with "doesn't have garlic"

please go back and read those posts to see why it's happening

also, you need a unique index or primary key on your table -- in the example you just gave, there are duplicate rows, and the same ingredent should never be included more than once in the same recipe

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

EdLentz
  • Locked
  • Question
Looking for suggestions on how to do this
Replies
4
Views
360
Chris Miller
Chris Miller
EdLentz
  • Locked
  • Question
How do I use a keyword in a query??
Replies
6
Views
490
EdLentz
EdLentz
bharons
  • Locked
  • Question
function argument value, type or count is invalid
Replies
3
Views
333
bharons
bharons
jawadfrhad
  • Locked
  • Question
how make row to column
Replies
4
Views
163
jawadfrhad
jawadfrhad
Arthur Lewis
  • Locked
  • Question
How to effect a NOWAIT in the LOCK TABLES statement?
Replies
0
Views
120
Arthur Lewis
Arthur Lewis

Part and Inventory Search

Sponsor

Back
Top