By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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);
SELECT p.id, name, COUNT(c.id) type_2_count
FROM parent p LEFT JOIN child c
ON c.parent_id = p.id AND c.type_id = 2
GROUP BY p.id;
| id | name | type_2_count |
|---|---|---|
| 1 | foo | 1 |
| 2 | bar | 2 |
| 3 | baz | 0 |
SELECT p.id, name,
COALESCE(t.cnt, 0) 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 | 0 |