Mysql returning incorrect COUNT when using more than one JOIN?

Question

I have 3 tables: NAMES, REGISTRATIONS, and RENEWALS. I'm using LEFT JOIN to join the 3 tables with a common ID.

I need to count the number of REGISTRATIONS of each user, as well as the number of RENEWALS. I've tried using different options in the GROUP BY field, but none seemed to work.

Here's the SELECT statement:

SELECT 
    names.name_id AS 'Names ID'
    ,names.name AS Name
    ,count(registrations.date) AS Registrations
    ,count(renewals.date) AS Renewals

FROM names

LEFT JOIN registrations

    ON names.name_id = registrations.name_id

LEFT JOIN renewals

    ON renewals.name_id = registrations.name_id

GROUP BY names.name_id, registrations.name_id, renewals.name_id;

And here are the 3 tables:

    TABLE: names
    +---------+------+
    | name_id | name |
    +---------+------+
    |       1 | Ana  |
    |       2 | John |
    |       3 | Paul |
    +---------+------+

    TABLE: registrations
    +-----------------+---------+---------------------+-------+
    | registration_id | name_id | date                | value |
    +-----------------+---------+---------------------+-------+
    |               1 |       1 | 2014-01-30 13:15:02 | 15    |
    |               2 |       2 | 2014-05-01 18:01:44 | 15    |
    |               3 |       2 | 2014-07-08 15:10:43 | 20    |
    |               4 |       3 | 2012-09-28 17:45:32 | 15    |
    |               5 |       3 | 2014-01-09 18:26:14 | 20    |
    |               6 |       3 | 2015-01-10 13:22:01 | 25    |
    +-----------------+---------+---------------------+-------+

    TABLE: renewals
    +------------+---------+---------------------+-------+
    | renewal_id | name_id | date                | value |
    +------------+---------+---------------------+-------+
    |          1 |       1 | 2015-01-30 00:00:00 | 5     |
    |          2 |       1 | 2016-02-12 00:00:00 | 5     |
    |          3 |       1 | 2015-06-01 00:00:00 | 5     |
    |          4 |       1 | 2013-11-24 00:00:00 | 5     |
    |          5 |       2 | 2015-01-27 00:00:00 | 5     |
    +------------+---------+---------------------+-------+

Here's the INCORRECT result I'm getting:

    +----------+------+---------------+----------+
    | Names ID | Name | Registrations | Renewals |
    +----------+------+---------------+----------+
    |        1 | Ana  |             4 |        4 |
    |        2 | John |             2 |        2 |
    |        3 | Paul |             3 |        0 |
    +----------+------+---------------+----------+

The CORRECT result I was expecting would be:

    +----------+------+---------------+----------+
    | Names ID | Name | Registrations | Renewals |
    +----------+------+---------------+----------+
    |        1 | Ana  |             1 |        4 |
    |        2 | John |             2 |        1 |
    |        3 | Paul |             3 |        0 |
    +----------+------+---------------+----------+

How can I fix the query to get a correct result?

Answer 1

Try this:

SELECT 
    names.name_id AS 'Names ID'
    ,names.name AS Name
    ,count(distinct registrations.registration_id) AS Registrations
    ,count(distinct renewals.renewal_id) AS Renewals
FROM names    
LEFT JOIN registrations    
    ON names.name_id = registrations.name_id    
LEFT JOIN renewals    
    ON renewals.name_id = registrations.name_id    
GROUP BY names.name_id, registrations.name_id, renewals.name_id;

Whenever I run into this type of issue, I find it helps to just run a select * query if your server can take it. Like this:

SELECT *
FROM names    
LEFT JOIN registrations    
    ON names.name_id = registrations.name_id    
LEFT JOIN renewals    
    ON renewals.name_id = registrations.name_id    ;

That will let you see what you are really counting.