the number of departments a given employee has within a mysql server closed

Your query actually does not seem far off, and might even be correct. However, I would use the distinct count of departments here:

SELECT e.eid, COUNT(DISTINCT d.did) AS cnt
FROM employee e
LEFT JOIN works w ON w.eid = e.eid
LEFT JOIN department d ON d.did = w.did
GROUP BY e.eid;

The reason we use the distinct count of departments here is because maybe a given employee had work experience in the same department more than once. Also note that I have rewritten your query to explicit join syntax (with LEFT JOIN). This is the preferred way to write joins versus the version you were using.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top