I have a Comment table that holds comments and replies, nested to only one level. When the ParentId is null, it is a standalone comment. When the ParentId is not null, it is a reply.

I have a Comment table that holds comments and replies, nested to only one level. When the ParentId is null, it is a standalone comment. When the ParentId is not null, it is a reply.

Here is the query I'm using to fetch the comments:

SELECT
    c.Id,
    c.ParentId,
    c.ReplyId,
    c.Comment,
    c.[Status],
    u.Id AS UserId,
    u2.Id AS ReplyToUserId
FROM 
    Post p
JOIN 
    Comment c ON p.Id = c.PostId
JOIN 
    [User] u ON c.UserId = u.Id
LEFT OUTER JOIN 
    [User] u2 ON c.ReplyToUserId = u2.Id
WHERE 
    p.Id = 33
ORDER BY 
    (CASE WHEN c.ParentId IS NULL THEN c.Id ELSE c.ParentId END), c.Id

I need help filtering out the comments where Status is Delete.

• Any deleted comment that another user has replied to, I want to include in the select. The comment will display in the UI as "Comment removed", but I don't want to disrupt the comment/reply flow in the UI to where excluding it may make other replies in the thread make no sense. In the example data above, these include Id = 22.

• Any deleted comment or reply that another user has not replied to can be removed from the select. Not showing these in the UI will not compromise the comment threads integrity. In the example data above, these include Id = 24, 26

• sql
• sql-server
• t-sql

• 2 Please show sample data for all those relevant tables – Charlieface Commented Mar 19 at 0:57
• 1 I'm also confused, as you seem to have multiple levels with ReplyId, but you don't seem to take that into account anywhere in your explanation or code. – Charlieface Commented Mar 19 at 1:03
• 2 Have you looked at using a recursive common table expression to walk through the replies one level at a time to build a tree and then pruning branches that consist only of Deleted comments? Anything left that is Deleted would display as "Comment removed". – HABO Commented Mar 19 at 2:26
• 2 We need to see your desired results – Dale K Commented Mar 19 at 5:56
• Perhaps, you have more than one level? For example 20<-22<-23. – ValNik Commented Mar 19 at 13:59

3 Answers 3

If we take your question at face value, that you do not want a multi-level nested answer, this query will get you the information you asked for.

Added a join to a summarized child comment sub query. This summarized sub query join makes the inclusion logic simple. The logic to change the comment is also simple.

SELECT
    c.Id,
    c.ParentId,
    c.ReplyId,
    case when c.[Status] = 'Deleted' and cr.[Status] = 'Active' then 'Comment Removed' else c.Comment end as Comment,
    c.[Status],
    ISNULL(cr.Status,c.[Status]) as ShouldBeIncludedStatus,
    u.Id AS UserId,
    u2.Id AS ReplyToUserId
    ,cr.[Status] as ChildStatus
FROM 
    Post p
JOIN 
    Comment c ON p.Id = c.PostId
JOIN 
    [User] u ON c.UserId = u.Id
LEFT OUTER JOIN 
    [User] u2 ON c.ReplyToUserId = u2.Id
--Add this join to a summarized version of the children comments.
LEFT OUTER JOIN
    (select ReplyId,[Status] from Comment cr where cr.Status = 'Active' group by cr.ReplyID,Status ) cr ON cr.ReplyID = c.ID
WHERE 
    p.Id = 33
    and ISNULL(cr.Status,c.[Status]) = 'Active'
--Order by clause can be simplified with and IsNull.
ORDER BY 
    ISNULL(c.ParentId,c.Id), c.Id

• For "The comment will display in the UI as "Comment removed"", see case expression for the Comment output field.
• For "Any deleted comment or reply that another user has not replied to can be removed" see where clause.

And here's a SQL Fiddle that also includes dummy versions of the referenced tables: SQL Fiddle with Mock reference tables.

I do suggest that you pay attention to the comments as they have great suggestions and follow up questions. If you do want multi-level nesting, you will need a CTE.

With a recursive Common Table Expression

You can use a recursive Common Table Expression,
where each active reply (or subreply or subsubreply) emits a token propagated from child to parent (even if the parent is Deleted) until reaching the top reply.
Then simply count which replies got token from this recursive walkthrough.

WITH hier AS
(
    SELECT Id, ReplyId FROM Comment WHERE Status = 'Active' AND ReplyId IS NOT NULL 
    UNION ALL
    SELECT p.Id, p.ParentId
    FROM hier c JOIN Comment p ON p.Id = c.ReplyId  
)
SELECT [your query]
WHERE Status = 'Active' OR EXISTS (SELECT 1 FROM hier WHERE hier.Id = c.Id)

A recursive CTE has the benefit of tagging Removed replies regardless of how many sublevels you have to go through to find an Active descendant reply,
thus allowing masking of offensive replies over multi-level spans:

Post
├ That's nice!
├ (Removed reply)
│ └ (Removed reply)
│   └ You too should really take politeness lessons, and notice how interesting it is that bla and bla…
└ Hey, I'm the last one!

(see each reply tagged in this fiddle)

How it works

The recursive CTE start with the query before the UNION as a first pass result buffer.
Then at each iteration, it plays the part after the UNION by reinjecting the result rows emitted by the previous iteration only (not all previous ones, just the last!) as contents for a table named after the recursive table (you noticed hier being both the named result (WITH), and the source (FROM): WITH hier AS (… UNION ALL SELECT … FROM hier …)).
Once no more rows are emitted, the iterations stops and all rows buffered get returned under that table alias, available to the following SELECTs.

Thus, inside the WITH, you'll get:

• at step 0: 21, 23 (with parents 20 and 22)
• at step 1: 20, 22 (with parents NULL and 20)
• at step 2: 20 (with parent NULL)

And there stops the recursion, and your results are returned as hier with contents:

on which you can do your EXISTS to know if the reply was refered to by an Active subreply.

Note that 20 appears twice (once from 21 → 20, once from 23 → 22 → 20):
I could have used a UNION instead of UNION ALL to get unique results,
but 1. it was easier to explain without a deduplication during the recursive part, and 2. with SELECT Id, COUNT(1) FROM hier GROUP BY Id you can now tell how many Active subreplies a reply has.

Try this query :

For standalone comments When ParentId is null

-->Include them only if they are not deleted.

For replies (ParentId IS NOT NULL):

Include them if:

-->They are not deleted.

-->OR they are deleted but have at least one active reply.

SELECT
    c.Id,
    c.ParentId,
    c.ReplyId,
    CASE 
        WHEN c.[Status] = 'Deleted' AND EXISTS (
            SELECT 1 
            FROM Comment r 
            WHERE r.ParentId = c.Id AND r.[Status] = 'Active'
        ) THEN 'Comment removed'
        ELSE c.Comment
    END AS Comment,
    c.[Status],
    u.Id AS UserId,
    u2.Id AS ReplyToUserId
FROM 
    Post p
JOIN 
    Comment c ON p.Id = c.PostId
JOIN 
    [User] u ON c.UserId = u.Id
LEFT OUTER JOIN 
    [User] u2 ON c.ReplyUserId = u2.Id
WHERE 
     (
        c.[Status] = 'Active' 
        OR (
            c.[Status] = 'Deleted' AND EXISTS (
                SELECT 1 
                FROM Comment r 
                WHERE r.ParentId = c.Id AND r.[Status] = 'Active'
            )
        )
    )
ORDER BY 
    (CASE WHEN c.ParentId IS NULL THEN c.Id ELSE c.ParentId END), c.Id;