How to get correct result with JOIN in sql?

Question

I wanted to get badge list with issued times.
This is brief info of my tables.

table Badge:

+----+-----------+
| id |  name     |
+----+-----------+
|  1 | professor |
|  2 | campaign  |
|  3 | test      |
+----+-----------+

table issue_history:

+----+-----------+--------+
| id | badge_id  |   ts   |
+----+-----------+--------+
|  1 |        1  |  0908  |
|  2 |        1  |  0909  |
|  3 |        3  |  0909  |
+----+-----------+--------*

To get result I used LEFT JOIN.

SELECT
    b.id,
    b.name,
    COUNT(*) AS issued_times
FROM
    badge b
LEFT JOIN
    issue_history h
ON
    b.id = h.badge_id
GROUP BY
    b.id

I expected result like below

+----+-----------+--------------+
| id |  name     | issued_times |
+----+-----------+--------------+
|  1 | professor |            2 |
|  2 | campaign  |            0 |
|  3 | test      |            1 |
+----+-----------+--------------+

But I got wrong result

+----+-----------+--------------+
| id |  name     | issued_times |
+----+-----------+--------------+
|  1 | professor |            2 |
|  2 | campaign  |            1 |
|  3 | test      |            1 |
+----+-----------+--------------+

As you can see, the issues times of campaign badge is 0.
But the result shows its value as 1.
How can I fix this issue?

Madhur Bhaiya · Accepted Answer · 2019-09-21T10:34:59.827

The issue here is that Count(*) counts all the rows in a particular group. Now, even when you don't have any history row for a specific badge, you you would still have one row for the badge corresponding to the base table badge. That is why, you were getting the count as 1.

To count the history rows, you need to Count() the badge_id from the history table. So if there is no matching row in the history table, badge_id on right side table would be NULL and COUNT(NULL) = 0:

SELECT
    b.id,
    b.name,
    COUNT(h.badge_id) AS issued_times
FROM
    badge b
LEFT JOIN
    issue_history h
ON
    b.id = h.badge_id
GROUP BY
    b.id, 
    b.name

Refer this official MySQL document for further understanding: https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html

Also, your GROUP BY usage was not valid; I added b.name in the GROUP BY for it to be valid. Check this to get an understanding: https://stackoverflow.com/a/34115425/2469308

How to get correct result with JOIN in sql?

1 Answers1