Count device children while retaining empty…

Return labid, labname, devicecount for every lab in the relational hierarchy mirror. Count devices whose labid references the lab, include zero-child labs, and order by lab_id. One row must represent one lab.

MySQL dialect.

sql
CREATE TABLE labs (lab_id INT PRIMARY KEY, lab_name VARCHAR(40) NOT NULL, department_id INT NOT NULL);
CREATE TABLE devices (device_id INT PRIMARY KEY, lab_id INT NOT NULL, device_name VARCHAR(40) NOT NULL);
INSERT INTO labs VALUES (10,'Robotics',1),(20,'Networks',1),(30,'Optics',2); INSERT INTO devices VALUES (1,10,'Camera'),(2,10,'Motor'),(3,30,'Lens');

Required ordering: lab_id ascending

Edge cases:

Empty labs remain with zero count
Several children count independently
Repeated device labels are not identities

Example 1

Input

INSERT INTO labs VALUES (10,'Robotics',1),(20,'Networks',1),(30,'Optics',2); INSERT INTO devices VALUES (1,10,'Camera'),(2,10,'Motor'),(3,30,'Lens');

Output

lab_id	lab_name	device_count
10	Robotics	2
20	Networks	0
30	Optics	1

Explanation: The left join retains each lab; counting matched device keys returns zero for a lab with no children.

Device identifiers are unique
Every device fixture references an existing lab
The model is three-level typed containment; it does not test arbitrary cycles

Hint 1

Use a lab-preserving join

Hint 2

Count the non-null device identifier rather than COUNT(*)

The left join retains each lab; counting matched device keys returns zero for a lab with no children.

Submit an attempt to unlock the solution. (Sign in first.)

Count device children while retaining empty labs

Related Problems

Input (stdin)

Output