• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 14
  • Last Modified:

SQL Full Text Search with FREETEXT and FREETEXTTABLE and RANKING challenge

I have set up a Full Text Catalog with a table called dbo.RetailerQADrafts.
The table has columns RetailerQADraftsID, RetailerQADraftsTitle and RetailerQADraftsBody.
I search for keywords in  RetailerQADraftsTitle and RetailerQADraftsBody with this code:
SELECT *
FROM dbo.RetailerQADrafts
WHERE FREETEXT(*, 'bob beck')

Open in new window


I get a nice result as the FTS looks at both the Title and Body for the keywords. Problem is there is no ranking with this method. The results are simply ordered by RetailerQADraftsID column.

I would like to do this but rank the search results and order by rank.
I then found some MS sample code using FREETEXTTABLE and so ran this code:
SELECT KEY_TBL.RANK, FT_TBL.RetailerQADraftsID, FT_TBL.RetailerQADraftsTitle, FT_TBL.RetailerQADraftsBody  
FROM dbo.RetailerQADrafts AS FT_TBL   
     INNER JOIN  
     FREETEXTTABLE(dbo.RetailerQADrafts, RetailerQADraftsBody,  
                    'bob beck') AS KEY_TBL  
     ON FT_TBL.RetailerQADraftsID = KEY_TBL.[KEY]  
ORDER BY KEY_TBL.RANK DESC

Open in new window


Works great! But, of course it only searches the Body text and not the title.
I can then use this code:
SELECT KEY_TBL1.RANK, FT_TBL1.RetailerQADraftsID, FT_TBL1.RetailerQADraftsTitle, FT_TBL1.RetailerQADraftsBody 
FROM dbo.RetailerQADrafts AS FT_TBL1   
     INNER JOIN  
     FREETEXTTABLE(dbo.RetailerQADrafts, RetailerQADraftsTitle,  
                    'bob beck') AS KEY_TBL1  
     ON FT_TBL1.RetailerQADraftsID = KEY_TBL1.[KEY]  
ORDER BY KEY_TBL1.RANK DESC 

Open in new window


as this searches the Title. But what I need is to somehow search BOTH the Title and Body, show the ranking of both results and make one table result without duplictaes. I would then SUM the rankings and then sort by total rank.

If I try a UNION to these two selects, of course I get duplicates:
SELECT KEY_TBL.RANK, FT_TBL.RetailerQADraftsID, FT_TBL.RetailerQADraftsTitle, FT_TBL.RetailerQADraftsBody  
FROM dbo.RetailerQADrafts AS FT_TBL   
     INNER JOIN  
     FREETEXTTABLE(dbo.RetailerQADrafts, RetailerQADraftsBody,  
                    'bob beck') AS KEY_TBL  
     ON FT_TBL.RetailerQADraftsID = KEY_TBL.[KEY]  
UNION all
SELECT KEY_TBL1.RANK, FT_TBL1.RetailerQADraftsID, FT_TBL1.RetailerQADraftsTitle, FT_TBL1.RetailerQADraftsBody 
FROM dbo.RetailerQADrafts AS FT_TBL1   
     INNER JOIN  
     FREETEXTTABLE(dbo.RetailerQADrafts, RetailerQADraftsTitle,  
                    'bob beck') AS KEY_TBL1  
     ON FT_TBL1.RetailerQADraftsID = KEY_TBL1.[KEY]  

Open in new window


I need to have this complete code solution run in a Stored Procedure in one fell swoop.
I hope I have described this well. If anyone has any ideas on how to do this please help!
0
SOTA
Asked:
SOTA
1 Solution
 
SOTAAuthor Commented:
OK, no help from anyone...so I ended up finding something that worked:
SELECT KEY_TBL.RANK, KEY_TBL2.RANK, FT_TBL.RetailerQADraftsID, FT_TBL.RetailerQADraftsTitle, FT_TBL.RetailerQADraftsBody  
FROM dbo.RetailerQADrafts AS FT_TBL   
     INNER JOIN  
     FREETEXTTABLE(dbo.RetailerQADrafts, *, @SearchTerm) AS KEY_TBL  
     ON FT_TBL.RetailerQADraftsID = KEY_TBL.[KEY]  
	 INNER JOIN  
     FREETEXTTABLE(dbo.RetailerQADrafts, *, @SearchTerm) AS KEY_TBL2  
     ON FT_TBL.RetailerQADraftsID = KEY_TBL2.[KEY]  
ORDER BY KEY_TBL.RANK DESC, KEY_TBL2.RANK DESC 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now