FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE Comment  (	PostID int, Id int	,	ParentId int	,	ReplyId int	,	Comment varchar(200)	,	Status varchar(200)	,	UserId int	,	ReplyToUserId	int)
CREATE Table Post (Id int)
CREATE TABLE [User] (Id int)

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

insert into Post
VALUES
(33)

INSERT INTO [User]
VALUES
 (17)
,(16)
,(9)
,(3)
,(13)
,(4)

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

Id	ParentId	ReplyId	Comment	Status	ShouldBeIncludedStatus	UserId	ReplyToUserId	ChildStatus
20	NULL	NULL	How's everyone doing?	Active	Active	17	NULL	Active
21	20	20	I'm good, how are you?	Active	Active	9	17	NULL
22	20	20	Comment Removed	Deleted	Active	16	17	Active
23	20	22	That's not nice	Active	Active	3	16	NULL
25	NULL	NULL	Just a random comment	Active	Active	13	NULL	NULL

(7 rows affected)

(1 rows affected)

(6 rows affected)

(5 rows affected)