Brian Brian - 2 years ago 79
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 = 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 = e.employeeid

Answer Source

Put a correlated sub-query in the select list.

SELECT d.department,
       (select COUNT(e.employeeid) from employee e where = 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 = e.departmentid or similar?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download