Basic Full Text search question

Hi

I am just starting to do a full text search on a sql 2000 database. My question is:

If I want to query a table Employee for 1. a particular word in a very large varchar field named "resume" 2 - a boolean field named "willtransfer" and 3. a field that holds the index of another table named "Cities" Cities just holds an index and cityname field of 15 characters.

do I create a fulltext index for all three fields or only the "resume" field since it is the only field that is being searched? What would a query for these 3 fields look like if I am searching for. I have seen no examples that combine multiple fields.

"speaks French" in resume, "true" for "willtransfer " field, and "Miami" in the cities table (there would need to be an inner join on the cityid field in both Employee and City table.

Thanks for any help on this. This is not the tables I am using, but this seemed like an easy example to explain.

smhaig

SELECT e.resume, e.willtransfer, c.cityname FROM Employee e
JOIN City c ON e.cityid = c.cityid
WHERE e.resume LIKE '%FRENCH%'
AND e.willtransfer = True
AND c.CityName = 'MIAMI'

I think maybe you might be looking for something like:

DECLARE @.mystring varchar(50)
SET @.mystring = 'french transfer=true Miami'

SELECT e.resume, e.willtransfer, c.cityname FROM Employee e
JOIN City c ON e.cityid = c.cityid
WHERE @.MyString LIKE '%FRENCH%'
AND @.MyString LIKE '%transfer=True%'
AND @.MyString@. = 'Miami'
AND e.resume LIKE '%FRENCH%'
AND e. willtransfer = 'True'
AND c.cityname = 'Miami'

I'm not sure if you want to concatenate all three fields or not.

Please see above

Adamus

|||

You only put a full text index on the 1 field you filter on the other columns using a standard where clause

i.e.

select employeeId

from employee

join city on employee.cityId = cities.cityId

where willtransfer = 1

and cityname = 'Miami'

and contains (employee.resume,'French')

This would match any resume that contains French you might want to include more complex search like "French NEAR speaks"