开发者

MySQL: Select comments and the number of replies they have

开发者 https://www.devze.com 2023-01-31 00:50 出处:网络
I have a \'Comments\' MySQL table that has the following fields: id text parent_id (NULL by default) Basically I want to select all the comments that have a NULL parent_id, and the total number of

I have a 'Comments' MySQL table that has the following fields:

  • id
  • text
  • parent_id (NULL by default)

Basically I want to select all the comments that have a NULL parent_id, and the total number of their replies. This comment system has only开发者_如何学Python one level of replies. The result would look something like:

-------------------------------------------------
| id | Text                     | total_replies |
-------------------------------------------------
| 1  | This is a comment        | 0             |
-------------------------------------------------
| 5  | another comment          | 3             |
-------------------------------------------------
| 7  | a different comment      | 1             |
-------------------------------------------------

Appreciate your help :)


This would probably be someting like:

select c.id, c.Text, count(reply.id) as total_replies
from comments c 
left join comments reply on c.id = reply.parent_id
where c.parent_id is null
group by c.id

If I understand correctly that the comments and replies are in the same table.


Assuming replies are in the same table:

SELECT c1.id, c1.text, COUNT(c2.id) total_replies
FROM comments c1 
LEFT JOIN comments c2 ON c2.parent_id = c1.id
WHERE c1.parent_id IS NULL
GROUP BY c1.id, c1.text
ORDER BY c1.id
0

精彩评论

暂无评论...
验证码 换一张
取 消