sql server full text search: I'm trying to write a search query for a forum. I've started with
this:

SELECT Posts.TopicID, ft.[RANK]
FROM Posts INNER JOIN FREETEXTTABLE (Posts, *, @SeachTerm, 100) AS ft
ON Posts.PostID = ft.[KEY]
ORDER BY ft.[RANK] DESC

No problem there, and it's fast and the results are really accurate.
Now I've expanded it to link it with actual topic records:

SELECT DISTINCT Topics.*, ft.[RANK]
FROM Topics INNER JOIN Posts
ON Topics.TopicID = Posts.TopicID
INNER JOIN FREETEXTTABLE (Posts, *, @SeachTerm, 100) AS ft
ON Posts.PostID = ft.[KEY]
ORDER BY ft.[RANK] DESC

Still works, and it's still fast, but I get duplicate topic records
despite the DISTINCT being there.

What am I missing here?

Thanks!

Jeff,
Could you provide the exact SQL Server version and OS platform that it is
installed on?
Please, post the full output of: SELECT @@version

I need to test a couple of things first, but is your TopicID a unique key?
Regards,
John



[quoted text, click to view]

It's SQL 2000 SP3a on a Win2k3 box... but that's not really relevant.

I figured it out with this:

SELECT Topics.TopicID, Topics.ForumID, Topics.Name, Topics.Title,
Topics.LastPostTime, Topics.Replies, Topics.LastPostName,
Topics.Views, MAX([FreeText].Rank)
FROM Topics
LEFT OUTER JOIN Posts ON Topics.TopicID = Posts.TopicID
INNER JOIN FREETEXTTABLE(Posts, *, @SeachTerm, 100) AS [FreeText] ON
[FreeText].[Key]=Posts.PostID
GROUP BY Topics.ForumID, Topics.TopicID, Topics.Name, Topics.Title,
Topics.LastPostTime, Topics.Replies, Topics.LastPostName, Topics.Views
ORDER BY MAX([FreeText].Rank) DESC

Because in the original query, the same topic records would have
different rank values, they would then be unique. The MAX function fixes
that, the only issue being the required need to group the fields and
specify them all.



*** Sent via Developersdex http://www.developersdex.com ***