为每个父记录选择具有条件的子记录数[重复]

这就是我所拥有的:

Table: parent

| id | name |
| -- | ---- |
|  1 | foo  |
|  2 | bar  |
|  3 | baz  |

Table: child

| id | parent_id | type_id |
| -- | --------- | ------- |
|  1 |         2 |       2 |
|  2 |         2 |       2 |
|  3 |      NULL |       2 |
|  4 |         1 |       1 |
|  5 |      NULL |       2 |
|  6 |      NULL |       1 |
|  7 |         1 |       2 |
|  8 |         3 |       1 |

我要选择所有父记录,以及每个父记录具有类型2的子记录数:

| id | name | type_2_count |
| -- | ---- | ------------ |
|  1 | foo  |            1 |
|  2 | bar  |            2 |
|  3 | baz  |            0 |

我试过这个:

SELECT p.id, name, COUNT(c.id) type_2_count
FROM parent p LEFT JOIN child c ON c.parent_id = p.id
WHERE c.type_id = 2
GROUP BY p.id;

| id | name | type_2_count |
| -- | ---- | ------------ |
|  2 | bar  |            2 |
|  1 | foo  |            1 |

但它错过了第三个记录。

还有这个:

SELECT p.id, name, t.cnt type_2_count
FROM parent p LEFT JOIN (
  SELECT parent_id, COUNT(*) as cnt
  FROM child
  WHERE type_id = 2
  GROUP BY parent_id
) t ON t.parent_id = p.id;

| id | name | type_2_count |
| -- | ---- | ------------ |
|  1 | foo  |            1 |
|  2 | bar  |            2 |
|  3 | baz  |         NULL |

但是 type_2_count 是 NULL 而不是 0 第三条记录。

这是我使用的模式:

CREATE TABLE IF NOT EXISTS parent (
  id INT AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO parent VALUES (1, 'foo'), (2, 'bar'), (3, 'baz');

CREATE TABLE IF NOT EXISTS child (
  id INT AUTO_INCREMENT,
  parent_id INT REFERENCES parent(id),
  type_id TINYINT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO child VALUES (1, 2, 2), (2, 2, 2), (3, NULL, 2), (4, 1, 1), (5, NULL, 2), (6, NULL, 1), (7, 1, 2), (8, 3, 1);