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

Navigation
0

Today I have a question maybe a bit basic but my mysql is a bit rusty.

I have 2 tables one with the names of users is another with a list of messages 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
    
asked by anonymous 28.11.2015 / 16:29

1 answer

1
  

Make use of subselect for this.

Here 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
    
28.11.2015 / 17:57
problem plugin camera cordova android Page Numbering Problems with iText