嵌套选择数据库sql查询

Question

how to order a values fetched from database??

SELECT * FROM comment;
------------------------------------
| commentid | content | read_unread|
------------------------------------
1              hello      Unread
2              hi         read    


Select * From replies;
//commentid Fk from table "  comment  " so it means row 1 from table comments has 2 replies
------------------------------------
| repnum | rep_content | commentid |
------------------------------------
1              see ya        1
2              ok            1

i want to show this in a table html/php like this

---

comment num |  Content | replies count
--------------------------------------------------   
 1              hello    2
 2              hi       0

//the "replies count 2" came from the table replies replied to commentid 1 from table comment

how can i show this in a single query sql order by the count of the replies count???

Answer 1

Try this :

SELECT a.commentid, a.content, count(b.repnum) as replies_count 
FROM comment a left join replies b on a.commentid = b.commentid 
GROUP by a.commentid