Recursive query with CTE - SUM of child columns for this parent

Question

I have a forum database that stores forum information in one column. Forum allows unlimited subforums.

Table Name - Forums

| ForumID | ParentForumID | Name | Description | TopicCount | ReplyCount | LastPost | 

Given ForumID as a parameter, I try SUM TopicCount and ReplyCount for all children. I am also trying to return the last LastPost that is listed as DATETIME .

I searched google and this forum and understand that I have to use a recursive CTE, but it's hard for me to understand the syntax. Here is my CTE job.

  WITH CTE (ForumID, ParentForumID) AS ( SELECT ForumID AS Descendant, ParentForumID as Ancestor FROM forums UNION ALL SELECT e.Ancestor FROM CTE as e INNER JOIN CTE AS d ON Descendant = d.ParentForumID ) SELECT e.Descendant, SUM(TopicCount) AS topics, SUM(ReplyCount) AS replys FROM CTE e WHERE e.Ancestor = 1 

Where 1 = Parameter for the forum identifier.

Thanks in advance for your help!

Answer 1

Everything is all right with you - you are very close :-)

Basically, you need to:

• determine the initial forum to be selected before CTE
• create an anchor request to a specific forum
• then TopicCount over all the children and summarize the counters TopicCount and ReplyCount

So your code should look something like this:

 DECLARE @RootForumID INT SET @RootForumID = 1 -- or whatever you want... ;WITH CTE AS ( -- define the "anchor" query - select the chosen forum SELECT ForumID, TopicCount, ReplyCount, LastPost FROM dbo.forums WHERE ForumID = @RootForumID UNION ALL -- select the child rows SELECT f.ForumID, f.TopicCount, f.ReplyCount, f.LastPost FROM dbo.forums f INNER JOIN CTE on f.ParentForumID = CTE.ForumID ) SELECT SUM(TopicCount) AS topics, SUM(ReplyCount) AS replys, MAX(LastPost) AS 'Latest Post' FROM CTE 

Of course, you can wrap this in a stored procedure that will take the original "root" ForumID as a parameter.