How do you write such a sql?

add the following two tables
comment to record comments and comments

CREATE TABLE `comment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `comment` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) 

the reply_to table records the relationship between replies and comments

CREATE TABLE `reply_to` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `response_id` int(11) NOT NULL, //idcommentid
  `comment_id` int(11) NOT NULL, //idcommentid
  PRIMARY KEY (`id`)
) 

Table comment data is as follows

clipboard.png
reply_to

clipboard.png

()sql

https://stackoverflow.com/que...
sql

clipboard.png
(~):

clipboard.png

Aug.20,2021

There is a problem with the design of the

table. The relationship between comments and responses is one-to-many, so there is no need for relational tables.

comment form comment
comment_id,text

reply table reply
reply_id,comment_id,reply_text

query
SELECT * FROM comment LEFT JOIN reply ON comment.comment_id = reply.comment_id


take a look, the first sql in the answer is correct, but no unanswered comments can be displayed. So it's good to rewrite it with outer join, but when you make a mistake, no one notices it. Then I am more supportive of putting it in a table.
the correct rewrite sql is:

SELECT
    c. COMMENT ,
    r. COMMENT AS reply
FROM
    `comment` c
LEFT OUTER JOIN reply_to rt ON c.id = rt.comment_id
LEFT OUTER JOIN `comment` r ON rt.response_id = r.id
Menu