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;
FROM department AS d
HAVING COUNT(e.employeeid) > 1
FROM employee AS e
WHERE d.id = e.employeeid
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?