根据条件，连接两个表和第二个表的记录计数。

I have following tables -

我有以下的表格

这是SQLFIDDLE

categories

类别

+-----------+-------------+
|  column   |    type     |
+-----------+-------------+
| id        | int(11)     |
| name      | varchar(40) |
| unit      | varchar(50) |
| is_active | tinyint(1)  |
+-----------+-------------+

and

和

products

产品

+-------------+---------------+
|   column    |     type      |
+-------------+---------------+
| id          | int(11)       |
| category_id | int(11)       |
| name        | varchar(40)   |
| base_rate   | decimal(10,2) |
| is_active   | tinyint(1)    |
+-------------+---------------+

I want to get list of categories along with count of number of products active. If no products are active for a category it should return 0.

我想要得到目录列表以及活动产品的数量。如果一个类别没有激活任何产品，它应该返回0。

Somewhat like table below -

有点像下面的表格

+----+--------+--------------+
| id |  name  | active_count |
+----+--------+--------------+
|  1 | Steel  |            1 |
|  2 | Cement |            2 |
+----+--------+--------------+

I have come up with following query -

我想到了以下问题-

SELECT c.id, c.name, c.unit, COUNT(p.category_id) as active_count 
FROM `categories` c 
JOIN `products` p
    ON c.id = p.category_id
WHERE ( p.is_active = 1 )
GROUP BY p.category_id;

This above query works only when there is at least one product active in each of category. If there are not products available it should return active_count as 0

以上查询仅在每个类别中至少有一个产品处于活动状态时才有效。如果没有可用的产品，它应该返回active_count为0。

How can I fix this ?

我该怎么解决这个问题呢?

Here is SQLFIDDLE

这是SQLFIDDLE

1 个解决方案

#1

Use LEFT JOIN instead of INNER JOIN:

使用左连接代替内连接:

SELECT c.id, c.name, c.unit, COUNT(p.category_id) as active_count 
FROM `categories` c 
LEFT JOIN `products` p
    ON c.id = p.category_id AND p.is_active = 1 
GROUP BY c.id;

It is also important to move predicate p.is_active = 1 from WHERE clause to ON, so that all records of categories table are returned by the query.

移动谓词p也很重要。is_active = 1 from WHERE子句to ON，以便查询返回category表的所有记录。

#1