Sort mysql table based on number of rows in another table

Question

I'm trying to sort a user table based on how many comments they have linked to them in a secondary comment-table, I figured a sub-select will be the best tool but I can't get the syntax correct.

Users table testdata:

id | user_id
1  | 1000
2  | 1001
3  | 1002

Comment table testdata

id | link_id
1  | 1002
2  | 1000
3  | 1002
4  | 1000
5  | 1002
6  | 1001
7  | 1000
8  | 1002

Expected sorted result in the first table would be:

id | user_id
3  | 1002
1  | 1000
2  | 1001

Any push in the right direction would be extremly helpful, thanks! =)

Answer 1

In fact, there is no need to use a sub-query. You can use a JOIN and ORDER BY the count:

SELECT 
    users.user_id, COUNT(comments.id) as total_messages
FROM 
    users
INNER JOIN 
    comments ON comments.link_id = users.id
GROUP BY 
    user_id
ORDER BY 
    COUNT(comments.id) DESC