Hi,

I have a forum and there is a thread table. When a user searches, I want to query the subject and the body of the thread. I pass in an XML string that contains the search query. I parse the XML and put it into a table, which I then cursor through and query the table. The problem is is that I'm getting redundant data back and putting DISTINCT into the select doesn't work. I know why I'm getting redundant data, I just don't know how to fix it. Any ideas on how to make this query work well?

Code:

DECLARE @searchXML VARCHAR(MAX)
SET @searchXML = '<searchTerms><term termID="b" /><term termID="s" /></searchTerms>'

--declare handle
DECLARE @handle INT
--prepare xml document
EXEC sp_xml_preparedocument @handle OUTPUT, @searchXML

SELECT * INTO #xml
FROM OPENXML (@handle, '/searchTerms/term',1)
WITH (termID varchar(100))

--create return table
CREATE TABLE #results (ThreadID INT, [Subject] VARCHAR(200),
DateCreated DATETIME, Username VARCHAR(50), UserID VARCHAR(13),
ReplyCount INT)

--cursor through xml-created table
DECLARE myCursor CURSOR FOR SELECT termID FROM #xml
DECLARE @term VARCHAR(100)

OPEN myCursor

FETCH NEXT FROM myCursor INTO @term

WHILE @@fetch_status = 0
BEGIN
INSERT INTO #results
SELECT
T.ID,
T.Subject,
T.DateCreated,
U.Username,
U.ID,
(SELECT COUNT(*) FROM tblReply R WHERE R.ThreadID = T.ID)
FROM tblThread T INNER JOIN tblUser U
ON T.UserID = U.ID
WHERE T.Subject LIKE ('%' + @term + '%') OR T.Body LIKE ('%' + @term + '%')

FETCH NEXT FROM myCursor
INTO @term
END

CLOSE myCursor
DEALLOCATE myCursor

--dispose of document
EXEC sp_xml_removedocument @handle
--return results table
SELECT * FROM #results