2

i have a table for Comments (ID, UserName, UserEmail, CommentBody, PostID)

and another table for its Replies (ID, UserName, UserEmail, SubCommentBody, ParentCommentID)

in this case i have only one nested comment/reply and each comment can have more than one replies but replies can't have any replies

for example:

Comments Table

ID    UserName    UserEmail    CommentBody    PostID
--    --------    ---------    -----------    ------
1     Dave        a@gmail.com  hello...       148
2     Alex        b@gmail.com  hi....         205
3     John        c@gmail.com  something..    205

Replies Table

ID    UserName    UserEmail    SubCommentBody    ParentCommentID
--    --------    ---------    -----------       ---------------
1     Jimmy       g@gmail.com  BlaBla...         1
2     Ben         h@gmail.com  Fine....          1
3     Jerry       m@gmail.com  something..       2

how i write a query to get comments and its replies? i have no idea :)

Hooman Limouee
  • 1,143
  • 2
  • 21
  • 43

2 Answers2

4

Use Only Replies

No need to differ comments and replies, since they serve the same purpose and structure (name, body, etc).

Use only Replies (ID, UserName, UserEmail, Body, Date, ParentReplyId, PostId).

No parent means a root comment. If it has a parent, place the reply under its parent.

Use Date to sort the replies.

Use Recursive Query

To load all replies for a given reply (hierarchical structure), use a recursive query. For example, load all replies of Id=13 reply, in SQL Server it's:

;WITH x AS
(
    -- anchor:
    SELECT Id, UserName, Body, ParentReplyId
    FROM Replies WHERE ParentReplyId = 13
    UNION ALL
    -- recursive:
    SELECT t.Id, t.FirstName, t.Body, t.ParentReplyId
    FROM x INNER JOIN Replies AS t
    ON t.ParentReplyId = x.Id
)
SELECT Id, FirstName, Body, ParentReplyId, FROM x

To load all replies for a given post, no need for recursive query, just load all replies linked to a given PostId:

SELECT * FROM Replies WHERE PostId = 100
Artem Novikov
  • 4,087
  • 1
  • 27
  • 33
0

You need to write a query that joins both tables together.

select * 
from Comments c
inner join Replies r on (c.ID = r.ParentCommentID)

would get you all of the comments and their replies.

Then look at ordering it, or maybe pulling all of the replies into a single field per comment. Lost of help on the internet for this type of thing.

SteveB
  • 1,474
  • 1
  • 13
  • 21
  • according to your reply, the reply is repeated once per comment – Hooman Limouee Sep 11 '16 at 11:22
  • of course it is as you are joining two tables together. If you want specific output then ask for it in your question, or provide an example of what output you expect. There are dozens of answers to this question that a simple Google search will reveal though. – SteveB Sep 11 '16 at 11:27