mysql连接两个不同的表

Question

I have two tables in my MySql database:

user    
- sid
- userid
- username

log
- sid
- userid
- login_time

As you can guess, there's a lot more records in log tables than in user table. I am using php to present these records on my website in a table format as shown below.

no  |  userid |  username  |  number of login  |
1   |  inzo   |  harvey    |        233        |
2   |  chae   |  schmidts  |        433        |
3   |  hibro  |  swainy    |        12         |

To get the number of login for each user, I can send another queries in a for statement. But it's consuming resources and making the server slow in the end.

Can I have this result in one single join query?

Answer 1

Yes you can, you have to use count the logins for each user with a group by

select  t1.userid, t1.username, count(t2.sid)
from    user t1
left join
        log t2
on      t1.userid = t2.userid
group by t1.userid, t1.username

The left join ensures you that users without logins will still be returned, wit 0 as count.

Edit

About the question in the comment: if you want to only count the logins with a specific flag value, you can just add where flag = x before the group by; if you want to have a separate count for each value of the flag, you have to add that flag to both group by and select.