I have tables like this: SQLFIDDLE
I’m trying to count how many products are in each category
x
SELECT `category_id`, `category_name`, IFNULL(count(*),0) cat_stat
FROM
`product_to_categories`
NATURAL JOIN
`categories`
GROUP BY `category_id`
it is almost working except IFNULL part.
I recieve:
+-------------+---------------+----------+
| category_id | category_name | cat_stat |
+-------------+---------------+----------+
| 1 | category1 | 5 |
+-------------+---------------+----------+
| 2 | category2 | 5 |
+-------------+---------------+----------+
| 3 | category3 | 6 |
+-------------+---------------+----------+
| 5 | category5 | 4 |
+-------------+---------------+----------+
but i would also like to get null results:
+-------------+---------------+----------+
| category_id | category_name | cat_stat |
+-------------+---------------+----------+
| 1 | category1 | 5 |
+-------------+---------------+----------+
| 2 | category2 | 5 |
+-------------+---------------+----------+
| 3 | category3 | 6 |
+-------------+---------------+----------+
| 4 | category4 | 0 |
+-------------+---------------+----------+
| 5 | category5 | 4 |
+-------------+---------------+----------+
thanks!
Advertisement
Answer
- NATURAL JOIN == NATURAL INNER JOIN whereas you need OUTER JOIN.
- Base table is
categories
, so it must be mentioned first (NATURAL LEFT JOIN takes the values for common columns from the most left table). - You must count definite column, not common column or total rows amount (they will give 1, not 0).
SELECT `category_id`,
`category_name`,
COUNT(`product_to_categories`.`category_id`) cat_stat
FROM `categories`
NATURAL LEFT JOIN `product_to_categories`
GROUP BY `category_id`