0

I followed the directions in this SO question to create a linq function to do a full text search on a food database i have. there are over 7000 records in this DB and with each keyword i add the results get larger as opposed to smaller.

here is the meat of my function:

SELECT *
FROM USDA_Foods AS FT_TBL 
    INNER JOIN FREETEXTTABLE(USDA_Foods,
    Shrt_Desc, 
    @searchWord) AS KEY_TBL
ON FT_TBL.foodId = KEY_TBL.[KEY]

How would i decrease the results with each new keyword by altering the code above?

Thanks

Community
  • 1
  • 1
peroija
  • 1,982
  • 4
  • 21
  • 37

2 Answers2

2

You get more results because it works looking for any of the words in the searched column, and not that contains all of the words, as you expect. You can't use AND or anything in FREETEXTTABLE, so you have to use something like CONTAINSTABLE, which allows you to use AND between the provided words.

CONTAINSTABLE (Transact-SQL)

Look at the example "I. Using CONTAINS with a logical operator (AND)" here:

CONTAINS (Transact-SQL)

The syntax is valid also for CONTAINSTABLE.

SELECT *
FROM USDA_Foods AS FT_TBL 
    INNER JOIN CONTAINSTABLE(USDA_Foods,
    Shrt_Desc, 
    @searchWord) AS KEY_TBL
ON FT_TBL.foodId = KEY_TBL.[KEY]

Your @searchWord should look like

'here AND there AND everywhere'

to look for text that contains here, there and everywhere.

JotaBe
  • 38,030
  • 8
  • 98
  • 117
  • i figured i needed `AND`'s but my choice of `FREETEXTTABLE` is what seems to have done me in. if I wanted to insert `AND` for the user instead of them typing it, is it best to add `AND` in my code behind or in the sql function (if that is even possible) – peroija Apr 16 '12 at 15:50
  • 1
    It's much easier in your code behind. Just replace spaces " " with " AND ". You shouldalso trim the start and end of the string, and replace multiple spaces together before "the final replace". You can also use Split, with the option that leaves out empty strings, and compose your "ANDed" kwywords string. Doing this in T-SQL is much, much harder. – JotaBe Apr 16 '12 at 15:55
0

Concatenate INNER JOINS for each search word.

SELECT * 
FROM USDA_Foods AS FT_TBL 
    INNER JOIN FREETEXTTABLE(USDA_Foods,
    Shrt_Desc, 
    @searchWord1) AS KEY_TBL1
ON FT_TBL.foodId = KEY_TBL1.[KEY]
    INNER JOIN FREETEXTTABLE(USDA_Foods,
    Shrt_Desc, 
    @searchWord2) AS KEY_TBL2
ON FT_TBL.foodId = KEY_TBL2.[KEY]
Jeff Bobish
  • 596
  • 4
  • 6
  • This supposes running severeal queries... If there are lots of several searchwords it will become really slow. It's better getting the result in a single query, using containstable and the 'AND' oepratod. – JotaBe Apr 16 '12 at 15:48