Algorithm behind Nested comments with SQL

Question

I want to know the algorithm behind child nested records are displayed within parent nested records for example

Comment 1 (parent record)
reply 1 (child record)
reply 2 (child record)
reply 3 (child record)
view all

Comment 2 (parent record)
reply 1 (child record)
reply 2 (child record)
reply 3 (child record)
view all

how is a query written to get the above results?

I'm assuming you mean *algorithm*, but even with that change your question is not very clear. What exactly is it that you want help with? — Lasse V. Karlsen, Apr 17 '15 at 08:26
Yes, I understand the concept of comments with replies, I'm asking what you want to know about that. What is it that you're having trouble with? — Lasse V. Karlsen, Apr 17 '15 at 08:29
i want to know how a query can be written to get this results — i_user, Apr 17 '15 at 08:32

score 2 · Accepted Answer · edited May 23 '17 at 11:59

You can use a Recursive Common Table Expression like the one below

;WITH comments AS 
(
    SELECT 1 as ID,'Comment 1' detail,NULL AS ParentID
    UNION ALL SELECT 2 as ID,'Comment 2',NULL AS ParentID
    UNION ALL SELECT 3 as ID,'Reply 1',1 AS ParentID
    UNION ALL SELECT 4 as ID,'Reply 2',3 AS ParentID
    UNION ALL SELECT 5 as ID,'Reply 3',4 AS ParentID
    UNION ALL SELECT 6 as ID,'Reply 4',2 AS ParentID
    UNION ALL SELECT 7 as ID,'Reply 5',6 AS ParentID
),comment_hierarchy AS 
(
    SELECT ID,detail,ID AS prid,0 AS orderid
    FROM comments
    WHERE ParentID IS NULL
    UNION ALL 
    SELECT c.ID,c.detail ,ch.prid as prid,ch.orderid + 1
    FROM comments c
    INNER JOIN comment_hierarchy ch
    ON c.ParentID = ch.ID
)
SELECT ID,Detail
FROM comment_hierarchy
ORDER BY prid,orderid asc

For more info refer

https://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx

CTE to get all children (descendants) of a parent

Algorithm behind Nested comments with SQL

1 Answers1