2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Zakaria
  • 21
  • 2
  • [This might help](http://stackoverflow.com/questions/508449/how-to-implement-not-like-as-the-search-condition-for-containstablefull-text-que). – Matt Gibson Sep 16 '11 at 13:19

1 Answers1

2

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
Matt Gibson
  • 37,886
  • 9
  • 99
  • 128