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
精彩评论