Mysql, number of rows in a table with values from another table

Question

today I have a question maybe a little basic but my mysql ta a little rusty.

I have 2 tables one with the users' names is another with a mailing list and wanted to know how many messages each user has.

table users

id | username
-------------
1  | joao
2  | nuno
3  | rui

table mensagens

id | msg  |user
------------------
1  | msg1 | joao
2  | msg2 | joao
3  | msg3 | nuno

the result I want (in order of number of messages)

user | msg_count
-------------
rui  | 0
nuno | 1
joao | 2

Answer 1

Make use of subselect for this.

Below is an example of SQL to help you:

SELECT username as user, (SELECT count(1) as qtd FROM mensagens WHERE mensagens.user = users.username) as msg_count FROM users ORDER BY msg_count ASC