I have the Table Comment below:
CREATE TABLE Comment
(
CommentID int,
Username nvarchar(50),
Content nvarchar(255),
ReplyID int,
primary key (CommentID),
)
ALTER TABLE dbo.Comment
ADD FOREIGN KEY (CommentID) REFERENCES dbo.Comment (CommentID) ON DELETE NO ACTION ON UPDATE NO ACTION,
And I want to query paging comment in detail product with data:
+----+----------+-------------+---------+
| ID | Username | Content | ReplyID |
+----+----------+-------------+---------+
| 1 | UserA | hello | null |
| 2 | UserB | hello | null |
| 3 | UserC | Hi UserA | 1 |
| 4 | UserD | Hello World | null |
| 5 | UserE | Hi UserB | 2 |
+----+----------+-------------+---------+
How can I show comments with paging, and displayPerPage = 2, example:
UserA: Hello
UserC: Hi UserA
UserB: Hello
UserE: Hi UserB
>>More Comments<<
Any help will be appreciated.
Solution 1: using Outer Join
You could use a query similar to the following to get the Comments in the correct order:
select case when c.ReplyId is not null then ' ' else '' end
+ UserName + ': ' + c.content Line
from Comment c
order by IsNull(c.ReplyId, c.CommentId), c.commentId