WHERE CONTAINS and multiple fields

Question

Post reply

WHERE CONTAINS and multiple fields

Rick Towns-868168

SSC Rookie

Points: 32
More actions
September 2, 2008 at 12:54 pm

#192310

[font="Courier New"]Hi everyone,
I've run into a road block using WHERE CONTAINS searching on multiple free-text index fields. Here's an example:
Say you’ve got a table called 'cars'...
+------+----------------+------------------------+
| ID | Make | Model |
+------+----------------+------------------------+
| 1 | Honda | Accord |
| 2 | Hyundai | Elantra |
| 3 | Honda | Civic |
| 4 | Honda | Fit |
| 5 | Hyundai | Accent |
+------+----------------+------------------------+
And you put a full-text index that includes both Make and Model fields. If you do a SQL query that says:
SELECT * FROM cars WHERE CONTAINS(*,' "Honda" AND "Accord" ')
I'd expect record number 1 to be returned - but NO records are returned. I can't use OR because it would then return records 1,3, and 4. I can't figure out how to get just record number 1 while using the free-text syntax. Is there any way to do it? This is for a site-search page that I built, so the actual search terms are coming in from a form, and then are being parsed into individual words to build the WHERE CONTAINS search phrase.
Thanks very much!
Rick Towns
Barrie, Ontario, CANADA
[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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

parackson Mr or Mrs. 500 Points: 532 More actions · Answer 1

I have done little with free-text but try typing out the actual column names

SELECT * FROM cars WHERE CONTAINS(*,' "Honda" AND "Accord" ')

SELECT * FROM cars WHERE CONTAINS((Make, Model),' "Honda" AND "Accord" ')

Rick Towns-868168 SSC Rookie Points: 32 More actions · Answer 2

I gave that a shot (specifying the field names), but I still don't get any records returned. I assume that SQL is thinking to compare my search phrase ("Honda" AND "Accord") against each indexed field individually, and then conglomerate the results into a results set. If that's the way its working, then I might be pooched!

parackson Mr or Mrs. 500 Points: 532 More actions · Answer 3

I know its not exactly using free-text but have you thought about the following

SELECT

*

FROM

Cars WITH (NOLOCK)

WHERE

Make = COALESCE(Make, @inMake)

AND Model = COALESCE(Model, @inModel)

Rick Towns-868168 SSC Rookie Points: 32 More actions · Answer 4

Well, yes, I could get it that way. But I'm using WHERE CONTAINS because this is a site search. Other than this one sticky problem, it is much more powerful than just using a regular WHERE clause. Maybe I'm looking to have my cake and eat it too?

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

If that is the behavior you really want, how about creating a persisted computed column concatenating the make and model together and create a fulltext index on that? I think that will work...

Rick Towns-868168 SSC Rookie Points: 32 More actions · Answer 6

Creating a concatenated search field and using the full-text index on that certainly worked. Is that my only alternative? I probably could make that work...