Description:
When I execute a query to generate a report to list a number of items in specific catalog collection, it doesn't return any rows that has no rows in the right table except in the first instance and not any other instances (I am expecting Category 2 and 4 to also appear). Expected outcome would show all rows in left table with "0" as a count.
My initial query showed:
mysql> select t1.id, t1.name, count(t2.id) as nbr from t1 left join t2 on t1.id=t2.t1_id group by t2.t1_id;
+----+------------+-----+
| id | name | nbr |
+----+------------+-----+
| 1 | Category 1 | 0 |
| 3 | Category 3 | 3 |
| 5 | Category 5 | 2 |
+----+------------+-----+
If I changed the group by to t1.id instead of t2.t1_id, it will show all rows.
mysql> select t1.id, t1.name, count(t2.id) as nbr from t1 left join t2 on t1.id=t2.t1_id group by t1.id;
+----+------------+-----+
| id | name | nbr |
+----+------------+-----+
| 1 | Category 1 | 0 |
| 2 | Category 2 | 0 |
| 3 | Category 3 | 3 |
| 4 | Category 4 | 0 |
| 5 | Category 5 | 2 |
+----+------------+-----+
I don't know, maybe I am using GROUP BY wrong. My understanding is that in the LEFT JOIN operation, if at any time the left table has rows that the right table doesn't, it should show up regardless the GROUP BY column.
How to repeat:
CREATE TABLE t1 (
id int(11) NOT NULL,
name varchar(10) NOT NULL
);
INSERT INTO `t1` (`id`, `name`) VALUES
(1, 'Category 1'),
(2, 'Category 2'),
(3, 'Category 3'),
(4, 'Category 4'),
(5, 'Category 5');
CREATE TABLE t2 (
id int(11) NOT NULL,
t1_id int(11) NOT NULL,
name varchar(20) NOT NULL
);
INSERT INTO `t2` (`id`, `t1_id`, `name`) VALUES
(1, 3, 'Diamond Ring'),
(2, 3, 'Stuffed Animal'),
(3, 5, 'Twin Bed'),
(4, 5, 'Night Stand'),
(5, 3, 'Lamp');
mysql> SELECT t1.id, t1.name, COUNT(t2.id) AS nbr FROM t1 LEFT JOIN t2 ON t1.id=t2.t1_id GROUP BY t2.t1_id;
+----+------------+-----+
| id | name | nbr |
+----+------------+-----+
| 1 | Category 1 | 0 |
| 3 | Category 3 | 3 |
| 5 | Category 5 | 2 |
+----+------------+-----+
3 rows in set (0.00 sec)
mysql> SELECT t1.id, t1.name, COUNT(t2.id) AS nbr FROM t1 LEFT JOIN t2 ON t1.id=t2.t1_id GROUP BY t1.id;
+----+------------+-----+
| id | name | nbr |
+----+------------+-----+
| 1 | Category 1 | 0 |
| 2 | Category 2 | 0 |
| 3 | Category 3 | 3 |
| 4 | Category 4 | 0 |
| 5 | Category 5 | 2 |
+----+------------+-----+
5 rows in set (0.00 sec)
Suggested fix:
I'd suggest that the results should return exactly the same in the 2nd select query regardless what column used in GROUP BY.
