SQL query for comments and replies

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.

Id	ParentId	ReplyId	Comment	Status	UserId	ReplyUserId
20	NULL	NULL	How's everyone doing?	Active	17	NULL
21	20	20	I'm good, how are you?	Active	9	17
22	20	20	Just shut up loser!	Deleted	16	17
23	20	22	That's not nice	Active	3	16
24	20	20	You're a loser too	Deleted	16	17
25	NULL	NULL	Just a random comment	Active	13	NULL
26	NULL	NULL	You're a random idiot	Deleted	4	NULL

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

Solution

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.