Error when the number of requests produced by category

advertisements

Category table

+----+------+
| id | name |
+----+------+
|  1 | cat1 |
|  2 | cat2 |
|  3 | cat3 |
+----+------+

Product table

+----+-------+--------+
| id | name  | cat_id |
+----+-------+--------+
|  1 | prod1 |      1 |
|  2 | prod2 |      1 |
|  3 | prod3 |      3 |
|  4 | prod4 |      1 |
|  5 | prod5 |      3 |
|  6 | prod6 |      1 |
+----+-------+--------+

This is my query:

 SELECT c.id, c.name, count( p.cat_id ) num
    FROM categories c
    INNER JOIN products p ON c.id = p.cat_id
    GROUP BY p.cat_id

This is result:

+----+------+-------+
| id | name | count |
+----+------+-------+
|  1 | cat1 |     4 |
|  3 | cat3 |     2 |
+----+------+-------+

Error now show count cat=2 How to fix get this result:

+----+------+-------+
| id | name | count |
+----+------+-------+
|  1 | cat1 |     4 |
|  2 | cat2 |     0 |
|  3 | cat3 |     2 |
+----+------+-------+

Full example here: http://sqlfiddle.com/#!9/f2cbb9/1


I think, you just have to change your join into a left join.

SELECT c.id, c.name, count( p.cat_id ) num
        FROM categories c
        LEFT JOIN products p ON c.id = p.cat_id
        GROUP BY p.cat_id