Full text search problem

Question

I have 2 tables nsk_UserInfo and Photos. I use full text search query to show result

SELECT nsk_UserInfo.User_ID
       , nsk_UserInfo.Name
       , nsk_UserInfo.Contact_Person
       , nsk_UserInfo.Address, Photos.Photo1 as 'Photo'
FROM nsk_UserInfo INNER JOIN Photos ON nsk_UserInfo.User_ID = Photos.User_ID 
WHERE FREETEXT ((nsk_UserInfo.*), 'My search term')

this query shows result but not give preference to record(row). what I can do? is it query correct or is there any changes in it? Please help me. It urgent.

Thank You Naresh

Answer 1

To get the rank, you will need to use FREETEXTTABLE(). Something like this:

SELECT nsk_UserInfo.User_ID,
       nsk_UserInfo.Name,
       nsk_UserInfo.Contact_Person,
       nsk_UserInfo.Address,
       Photos.Photo1 as Photo,
       KeyTable.RANK
FROM nsk_UserInfo
  INNER JOIN Photos
    ON nsk_UserInfo.User_ID = Photos.User_ID
  INNER JOIN FREETEXTTABLE((nsk_UserInfo.*), 'My search term') AS KeyTable
     ON nsk_UserInfo.User_ID = KeyTable.[KEY]
ORDER BY KeyTable.RANK DESC;

See more info at http://msdn.microsoft.com/en-us/library/ms142583.aspx.