October 19, 2005 at 6:51 am
Hi,
I'm having problems with a query (see below)... It's supposed to return all rows from my Forum table, + information like how many starting threads exist for each forum, the last post for each forum.
The problem is that it actually returns each forum multiple times. More specific, if there are 3 ForumMessage rows in forum X, forum X appears 3 times in my result set.
I understand that the problem lies in the joins somehow, but I can't figure out what it is exactly.
Help would be highly appreciated!
Thanks!
-------
ALTER PROCEDURE dbo.Forum_GetAll
AS
/* SET NOCOUNT ON */
SELECT DISTINCT F.ForumID, F.ForumCategoryID, F.Name, F.Description, F.FromDate, F.ToDate,
TC.Threads, MC.Posts, LP.LastPostDate, LP.MemberID AS LastPostByMemberID, LP.ForumMessageID AS LastPostID,
LP.DisplayName AS LastPostUsername
FROM Forum F
RIGHT JOIN
(
SELECT COUNT(*) AS Threads, ForumID
FROM ForumMessage
WHERE ParentForumMessageID = 0
GROUP BY ForumID
) TC ON F.ForumID = TC.ForumID
LEFT JOIN
(
SELECT COUNT(*) AS Posts, ForumID
FROM ForumMessage
WHERE ParentForumMessageId > 0
GROUP BY ForumID
) MC ON F.ForumID = MC.ForumID
LEFT JOIN
(
SELECT MAX(PostedDate) AS LastPostDate, MemberID, ForumID, ForumMessageID
FROM ForumMessage
GROUP BY ForumID, MemberID, ForumMessageID
) LP ON F.ForumID = LP.ForumID
LEFT JOIN
(
SELECT MEM.MemberID, MEM.DisplayName
FROM Member MEM
) M ON LP.MemberID = M.MemberID
ORDER BY F.Name
RETURN
October 19, 2005 at 6:59 am
Just split this into 2 recordsets. The first one is th elist of messages to present. The other will be for statistical info.
October 19, 2005 at 7:12 am
What do you mean? The query is used in a Stored Procedure and picked up by a .NET business layer.
I don't want to have to handle two record sets in my business code. I want it all to come from one SP/query.
October 19, 2005 at 7:15 am
I'll need much more info. Make sure you read this ALL befor posting back.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply