根据条件将两个表以及第二个表中的记录数联接在一起

Question

我有以下表格-

这是SQLFIDDLE

分类

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

和

产品展示

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

我想获取类别列表以及活跃产品的数量。如果某个类别的产品均未激活，则应返回0。

有点像下面的表格-

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

我想出了以下查询-

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;

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

我怎样才能解决这个问题 ？

这是SQLFIDDLE

Answer 1

使用LEFT JOIN代替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;

将谓词p.is_active = 1from WHERE子句移至ON，这也很重要，以便查询返回表的 所有 记录categories。