QOD 8/12/03

Question

Post reply

QOD 8/12/03

Frank Kalis

SSC Guru

Points: 111183
More actions
August 12, 2003 at 1:16 am

#106436

Hi Mark,
nice one this QOD !
Obviously I'm not the only one for whom the query didn't return the correct number of records.
I've followed the example step by step. Any rationale on this ???
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

iastrebov SSCrazy Points: 2097 More actions · Answer 1

I don't understand, why "Homemade Chicken Soup" must be returned on the query:

SELECT RecipeName FROM recipes WHERE CONTAINS(*, 'chicken AND NOT soup')

Can anybody clarify this?

Iouri.

Keiths123 SSCommitted Points: 1789 More actions · Answer 2

Good question! Shows how many people out there actually use 'contains' in their databases....

For clarification of why it returns what it does, I am assuming that the contains statement searches all the columns (the * in the statement) for all the words in the second part of the statement: chicken; soup; and; not;

If I am wrong, can someone please correct me?

Cheers

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

I use CONTAINS, that's why I'm wonderning and need some explanation

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

MikeTomkies Hall of Fame Points: 3185 More actions · Answer 4

I thought it would return all five rows because they all contain Chicken,Soup.Why is the answer 4 rows and why will the "Soup a la Chicken"-"Soup made from chicken." recipe not be returned?

Aadomm Old Hand Points: 306 More actions · Answer 5

My deduction is that the query is assessing the contains condition on all columns seperately and accepting all results where any one column passes.

mccork SSC-Insane Points: 22288 More actions · Answer 6

I spent a couple of hours scratching my head at a client's site, over a similar (non food related) query and the unexpects results I was getting.

When you do "CONTAINS (*...", the condition is tested individually against each participating FT column in the table, and returns TRUE if it's TRUE for any of those columns.

Where I was going wrong was thinking the condition was tested against the combined values of the participating columns.

The row "Soup a la Chicken, Soup made from chicken" is the only one where the condition is FALSE for both columns, therefore it's the only one excluded from the result set.

The condition is TRUE for at least one of the columns in the other 4 rows in the table.

One way of getting a "reliable" result is 2 separate CONTAINS clauses:

 

    SELECT RecipeName FROM recipes

        WHERE CONTAINS(*, 'chicken')

        AND NOT CONTAINS(*, 'soup')

or, to get rid of the soup-like recipes:

 

    SELECT RecipeName FROM recipes

        WHERE CONTAINS(*, 'chicken')

        AND NOT CONTAINS(*, 'soup OR broth OR "consomm*"')

PS. Aadomm is spot on. And I think Keiths123 is too.

Cheers,

- Mark

Edited by - mccork on 08/12/2003 03:14:22 AM

Cheers,
- Mark

MikeTomkies Hall of Fame Points: 3185 More actions · Answer 7

I am Confused. The stament the colums should contain is 'chicken AND NOT soup' The AND NOT are not operators but just words to look for in the colums i.e for AND NOT to be an operator phrase Should Be '"Chicken" AND NOT "Soup"'.

From Marks explanation then all the rows do not contain the words And;NOT so therefore no rows would be returned.

Ritch Ten Centuries Points: 1044 More actions · Answer 8

From BOL

quote:
Phrases and predicates can be combined to search for combinations of words and phrases. For example, you can search for all rows that contain either ice skating or hockey but not references to the Olympics. The WHERE clause for using the CONTAINS predicate looks like this:
WHERE CONTAINS (*, '("ice skating" or hockey) AND NOT olympics')

I chose my answer based on this and got it wrong...why do the SQL gods mock me....

"I didn't do anything it just got complicated" - M Edwards

mccork SSC-Insane Points: 22288 More actions · Answer 9

Mike,

Honest, I did test it before submitting it... I didn't want to be caught out by 30,000 irate DBA's.

The 'chicken AND NOT soup' syntax is ok. Either SQL, or the Indexing service, knows that CHICKEN and SOUP are simple terms, and the 'AND NOT' is a search condition.

It's only in the case of non-simple terms. eg. the prefix term "consomm*" (see example earlier in this thread) that the double-quotes are needed.

Cheers,

- Mark

Cheers,
- Mark

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

quote:
Honest, I did test it before submitting it... I didn't want to be caught out by 30,000 irate DBA's.

get some tar and feather

Cheers,

Frank

P.S. what I like most, is that SiteOwners give credit to the one submitting a question.

BTW, Mark, when you are the one who submits a question, are you allowed to answer it? That's a serious question!

Edited by - a5xo3z1 on 08/12/2003 05:57:08 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

skrilla99 SSCertifiable Points: 5795 More actions · Answer 11

Yowza...

I never thought that it worked on all FT columns individually.

Certainly highlights the difference between thinking like a person and thinking like a machine.

Certainly had a hard time wrapping my head around this one. Thanks Mark!

Dan B

mccork SSC-Insane Points: 22288 More actions · Answer 12

Frank,

You betcha. 3 points for me!

Cheers,

- Mark

Cheers,
- Mark

cwbutler SSC-Addicted Points: 499 More actions · Answer 13

ouch, my brain hurts - and worse yet I'm STILL not sure I understand it 😉

--woody

C. Woody Butler, MCP

cwbutler@cebec.com

C. Woody Butler, MCP
cwbutler@cwoodybutler.com

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

quote:
You betcha. 3 points for me!

I hate you !!!

Well, obviously a great motivation to submit questions

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]