cdc catz cdc catz - 1 month ago 18
PHP Question

nested select database sql query

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

Try it.

SELECT a.commentid, a.content, count(*) as replies_count
FROM comment a
LEFT JOIN replies b on a.commentid=b.commentid
GROUP BY a.commentid, a.content
Comments