MYSQL LEFT JOIN and COUNT and GROUP BY

Question

I found several threads concerning that task, but all don't really help me, or I'm not experienced enough to understand it.

I am using mysql and have two tables user and user_rh. I want a list (ordered by names) of usernames with the correspond number of entries in user_rh.

      `user`                `user_rh`
------------------     ------------------
| `uid` | `name` |     | `uid` | `zaid` |
------------------     ------------------
|   1   | Bob    |     |   2   |    4   |
|   2   | John   |     |   2   |    7   |
|   3   | Fred   |     |   3   |    2   |
|   4   | Peter  |     ------------------
------------------

So the result should look like:

--------------------
| `name` | `count` |
--------------------
| Bob    |    0    |
| Fred   |    1    |
| John   |    2    |
| Peter  |    0    |
--------------------

I tried this query:

SELECT
    `user`.`name`,
    `user_rh`.`uid`
FROM
    `user`
LEFT JOIN
    `user_rh`
ON (`user_rh`.`uid`=`user`.`uid`)
ORDER BY
    `user`.`name`

It is going in the right way, but this will not return count values:

------------------
| `name` | `uid` |
------------------
| Bob    |  NULL |
| John   |   2   |
| John   |   2   |
| Fred   |   1   |
| Peter  |  NULL |
------------------

I thought I just add the COUNT() and GROUP BY commands:

SELECT
    `user`.`name`, 
    COUNT(`user_rh`.`uid`) AS `count`
FROM
    `user`
LEFT JOIN
    `user_rh`
    ON (`user_rh`.`uid`=`user`.`uid`)
GROUP BY
    `user_rh`.`uid`
ORDER BY
    `user`.`name`

But it shows me something like this. First it delivers a sorted list of names that have count!=0 and the last entry of the list is the first entry of table user which has count=0.

------------------
| `name` | `uid` |
------------------
| John   |   2   |
| Fred   |   1   |
| Bob    |   0   |
------------------

I think, I'm just combining the commands in a wrong way.

Solution 1

Your 2nd query is fine. Just group by the user from the first table. Otherwise you would turn your left join into an inner join

SELECT
    u.name, 
    COUNT(rh.uid) AS cnt
FROM
    `user` u
LEFT JOIN
    user_rh rh
    ON (rh.uid = u.uid)
GROUP BY
    u.uid, u.name
ORDER BY
    u.name

Solution 2

1. it does not transform your left join to an inner join. It is just grouping by your uid from user_rh when linked to user. You have 3 different uid (NULL, 2 and 1) that's why you have only 3 lines in your result.

2. when you are using grouping clause, you can select only field with grouping aggregate (like COUNT, SUM, AVG..) or field available in the group clause. You can omit the grouping but Mysql will do the grouping itself but results can be unreliable since it will choose the first one available. That's why you have Bob displayed in your 3 lines result.

Comments

[Sven Richter]

Thanks............!!!! That works.......... Two more questions to understand it correctly. 1) Why does the GROUP BY user_rh.uid transforms the LEFT JOIN into an INNER JOIN? 2) Why is the additional user.name in the GROUP BY command?

[juergen d]

1. If you group by something it must be there - and a LEFT JOIN is exactly for the opposite - join a table and don't reduce the data even if the relation could not be estabished. 2. You should always group all the column you select and don't aggregate. Only MySQL allows to do otherwise.