I am trying to display the count of products along with the category name in the category list.
I was able to achieve this with JOIN, but not all categories are displayed.
I mean the categories with 0 products were not displayed, but I changed the JOIN to LEFT JOIN which displayed only one category with 0 products. There are two more.
SELECT c.id , c.name , count( p.category_id ) product_count FROM category c LEFT JOIN products p ON c.id = p.category_id GROUP BY p.category_id
This is the query I am trying to execute.
If anyone has any idea, please guide me.
Your query should fail because the
GROUP BY clause is inconsistent with the
SELECT columns —
c.id is not the same as
p.category_id. (Older version of MySQL do accept this syntax.)
You are aggregating by a column that has a
NULL value when there is no match. You really want to aggregate by a column in the
category table. I would recommend:
SELECT c.id, c.name , count( p.category_id ) AS product_count FROM category c LEFT JOIN products p ON c.id = p.category_id GROUP BY c.id, c.name;
Note that the
GROUP BY columns match the unaggregated
SELECT columns. Also, the
as id and
as name are redundant, so I removed them.