CODEWITHSUNDEEP
sql-serversql-server-2008full-text-searchcontainstable
Zakaria
Zakaria

Reputation: 21

SQL Server full text search issue

I have a problem with full-text search results, I need to find rows that contain " spray " in name column and not contains " men " in description column.

select top 10 ftt.RANK, ID, name, description
from mod_product_all_fields
INNER JOIN containstable(mod_product_all_fields,(name),' ("spray") ' )  as ftt
    ON mod_product_all_fields.ID=ftt.[KEY]
INNER JOIN containstable(mod_product_all_fields,(description),' not ("men")   ')  as ftt2
    ON mod_product_all_fields.ID=ftt2.[KEY] 
ORDER BY ftt.RANK DESC

This query does not execute correctly, the debugger displays that I have a syntax error near " not ('men') ".

If you have a solution for my issue please tell me

Thanks

Upvotes: 2

Views: 222

Answers (1)

Matt Gibson
Matt Gibson

Reputation: 38238

Expanding on my pointer to the earlier answer about how to search for NOT <a word>, I think this should do the trick:

select top 10 ftt.RANK, ID, name, description
from mod_product_all_fields
INNER JOIN containstable(mod_product_all_fields,(name),' ("spray") ' )  as ftt
    ON mod_product_all_fields.ID=ftt.[KEY]
LEFT JOIN containstable(mod_product_all_fields,(description),' ("men") ')  as ftt2
    ON mod_product_all_fields.ID=ftt2.[KEY] 
WHERE
    ftt2.KEY IS NULL -- Eliminate matches on "men"
ORDER BY ftt.RANK DESC

Upvotes: 2

Related Questions