Comments Parent-Child query with indentation

I’ve been trying to retrieve comments to articles in a pretty common blog fashion way. Here’s my sample code:

-- ----------------------------
-- Sample Table structure for [dbo].[Comments]
-- ----------------------------
CREATE TABLE [dbo].[Comments] (
[CommentID] int NOT NULL ,
[AddedDate] datetime NOT NULL ,
[AddedBy] nvarchar(256) NOT NULL ,
[ArticleID] int NOT NULL ,
[Body] nvarchar(4000) NOT NULL ,
[parentCommentID] int NULL 
)


GO

-- ----------------------------
-- Sample Records of Comments
-- ----------------------------
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body],     [parentCommentID]) VALUES (N'1', N'2011-11-26 23:18:07.000', N'user', N'1', N'body', null);
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body],   [parentCommentID]) VALUES (N'2', N'2011-11-26 23:18:50.000', N'user', N'2', N'body', null);
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body],    [parentCommentID]) VALUES (N'3', N'2011-11-26 23:19:09.000', N'user', N'1', N'body', null);
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'4', N'2011-11-26 23:19:46.000', N'user', N'3', N'body', null);
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'5', N'2011-11-26 23:20:16.000', N'user', N'1', N'body', N'1');
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'6', N'2011-11-26 23:20:42.000', N'user', N'1', N'body', N'1');
GO
INSERT INTO [dbo].[Comments] ([CommentID], [AddedDate], [AddedBy], [ArticleID], [Body], [parentCommentID]) VALUES (N'7', N'2011-11-26 23:21:25.000', N'user', N'1', N'body', N'6');
GO

-- ----------------------------
-- Indexes structure for table Comments
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dbo].[Comments]
-- ----------------------------
ALTER TABLE [dbo].[Comments] ADD PRIMARY KEY ([CommentID])
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[Comments]
-- ----------------------------
ALTER TABLE [dbo].[Comments] ADD FOREIGN KEY ([parentCommentID]) REFERENCES [dbo].   [Comments] ([CommentID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

I thought I could use a CTE query to do the job like this:

  • Finding a relevant subset of a set of 3d points
  • sql server 2012 allowing remote connection
  • Views vs. inline subqueries SQL Server 2005/2008
  • How to disable comment when using SMO
  • How do I use T-SQL Group By
  • SQOOP EXPORT : java.sql.BatchUpdateException: Parameter #1 has not been set
  • WITH CommentsCTE(CommentID, AddedDate, AddedBy, ArticleID, Body, parentCommentID, lvl,  sortcol)
    AS
    (
    SELECT CommentID, AddedDate, AddedBy, ArticleID, Body, parentCommentID, 0,   cast(CommentID as varbinary(max))
    FROM Comments
    UNION ALL
    SELECT P.CommentID, P.AddedDate, P.AddedBy, P.ArticleID, P.Body, P.parentCommentID,    PP.lvl+1,
    CAST(sortcol + CAST(P.CommentID AS BINARY(4)) AS VARBINARY(max))
    FROM Comments AS P
    JOIN CommentsCTE AS PP
    ON P.parentCommentID = PP.CommentID
    )
    SELECT
    REPLICATE('--', lvl)
    + right('>',lvl)+ AddedBy
    + ' :: '
    + Body,
    CommentID,
    parentCommentID,
    lvl
    FROM CommentsCTE
    WHERE ArticleID = 1
    order by sortcol
    go
    

    but the results have been very disappointing so far, and after days of tweaking I decided to ask for help. I was looking for a method to display hierarchical comments to articles like it happens in blogs.

    [edit]
    The problem with this query is that I get duplicates because I couldn’t figure out how to properly select the ArticleID which I want comments from to display. I’m also looking for a method that sorts children entries by date within a same level.
    An example of what I’m trying to accomplish could be something like:

    (ArticleID[post retrieved])
    -------------------------
    -------------------------
    (Comments[related to the article id above])
    first comment[no parent]
    --[first child to first comment]
    --[second child to first comment]
    ----[first child to second child comment to first comment]
    --[third child to first comment]
    ----[first child to third child comment to first comment]
    ------[(recursive child): first child to first child to third child comment to first comment]
    ------[(recursive child): second child to first child to third child comment to first comment]
    second comment[no parent]
    third comment[no parent]
    --[first child to third comment]
    

    I kinda got myself lost in all this mess…I appreciate any help or simpler ways to get this working. Thanks

  • Delete a row from a SQL Server table
  • sp_getapplock transaction within transaction
  • Set a existing column of MS SQL table as NOT NULL
  • Issue in getting connectionstring from ADO Connection in SSIS
  • SQL, How to get the year that an event occurs for the second time
  • FOR XML multiple control by attribute in tree concept
  • One Solution collect form web for “Comments Parent-Child query with indentation”

    You were very close. I’ve made two modifications to your recursive query:

    WITH CommentsCTE (CommentID, AddedDate, AddedBy, ArticleID, Body, parentCommentID, lvl, Thread)
    AS
    (
        SELECT  CommentID, 
                AddedDate, 
                AddedBy, 
                ArticleID, 
                Body, 
                parentCommentID, 
                0,
                ROW_NUMBER() over (order by CommentID) as Thread
        FROM @Comments
        where parentCommentID is null
    
        UNION ALL
    
        SELECT  P.CommentID, 
                P.AddedDate, 
                P.AddedBy, 
                P.ArticleID, 
                P.Body, 
                P.parentCommentID,    
                PP.lvl+1,
                PP.Thread
        FROM @Comments AS P
        JOIN CommentsCTE AS PP ON P.parentCommentID = PP.CommentID
    )
    SELECT  REPLICATE('--', lvl) + right('>',lvl)+ AddedBy + ' :: ' + Body,
            CommentID,
            parentCommentID,
            lvl,
            AddedDate,
            Thread
    FROM CommentsCTE
    WHERE ArticleID = 1
    order by Thread, CommentID
    

    First, adding

    where parentCommentID is null
    

    to your anchor query eliminates your duplicates. Second, to sort them properly, you need a Thread identifier. I added a row number to your anchor query to create the thread number. That allows you to sort the results properly.

    Here is an example of it in action.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.