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!
SOTAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.