Mysql how to do this query

SELECT u.username, count(t.tid) as total
FROM tbl2 t
JOIN users u ON t.userid = u.us开发者_Go百科erid
GROUP BY t.userid

The above query works fine and all, it returns the number of total task for each user that has atleast one task in the tbl2 table.

But what i want to do is return all users, Even if the user doesn't have any records associated to him in the second tbl2 table. I want the total to show as 0 for those users who doesn't have any records, how can i accomplish this?

The problem with the other answers given is that you want to select all users that have no associated records; using a LEFT JOIN, the users table is on the wrong (nullable) side of the join. You could replace that LEFT JOIN with a RIGHT JOIN, but that syntax always feels unintuitive to me.

The standard answer is to reverse the order of the tables while using a LEFT JOIN:

SELECT u.username, count(t.tid) as total
FROM users u
LEFT JOIN tbl2 t ON t.userid = u.userid
GROUP BY u.username

Note that it's better practice (and, in some DBMSes, required) to group on the non-aggregated columns in the SELECT list, rather than grouping on userid and selecting username.

You should use a LEFT JOIN insteed of an INNER JOIN (default type of join), something like this:

SELECT u.username, count(t.tid) as total
FROM tbl2 t
LEFT JOIN users u ON t.userid = u.userid
GROUP BY t.userid

You'd want to use a LEFT JOIN then instead

SELECT u.username, count(t.tid) as total
FROM tbl2 t
LEFT JOIN users u ON t.userid = u.userid
GROUP BY t.userid

LEFT JOIN will join the table and show null column results for tbl2 if an associated entry doesn't exist.