Brian Brian - 5 months ago 15
MySQL Question

writing a left join as a subquery

How can I put this left join query as a subquery instead?

SELECT d.department, COUNT(e.employeeid)
FROM department d
LEFT JOIN employee e
ON d.id = e.employeeid
GROUP BY d.department
ORDER BY d.department;


This is what I've tried, but doesn't work.

SELECT d.department
FROM department AS d
HAVING COUNT(e.employeeid) > 1
(
SELECT COUNT(e.employeeid)
FROM employee AS e
WHERE d.id = e.employeeid
);

Answer

Put a correlated sub-query in the select list.

SELECT d.department,
       (select COUNT(e.employeeid) from employee e where d.id = e.employeeid)
FROM department d
ORDER BY d.department

Perhaps you need to do SELECT DISTINCT to avoid duplicate rows.

Edit: That join condition seems a bit strange. Shouldn't it be d.id = e.departmentid or similar?