AdRock Posted November 17, 2007 Share Posted November 17, 2007 I have been struggling to create this query all dat and am ready to give up I want to select all employee_ids who manages a department with more than 5 employees Employee table ------------------- employee_id (primary key) Manages table ------------------ employee_id (foreign key) dept_id (foreign key) Works_in table ------------------ employee_id (foreign key) dept_id (foreign key) Department table ---------------------- dept_id (primary key) Link to comment https://forums.phpfreaks.com/topic/77761-solved-struggling-with-query-select-all-employee_ids-who-manages-a-department-with-more/ Share on other sites More sharing options...
Barand Posted November 18, 2007 Share Posted November 18, 2007 SELECT e.employee_id, COUNT(*) as underlings FROM Employee e INNER JOIN Manages m ON e.employee_id = m.employee_id INNER JOIN Works_in w ON m.dept_id = w.dept_id GROUP BY e.employee_id HAVING underlings > 5 Link to comment https://forums.phpfreaks.com/topic/77761-solved-struggling-with-query-select-all-employee_ids-who-manages-a-department-with-more/#findComment-393698 Share on other sites More sharing options...
AdRock Posted November 18, 2007 Author Share Posted November 18, 2007 Thanks Barand That sorted out half the problem. I have added an extra inner join sp now it shows which dept_id the count belongs to SELECT e.employee_id, COUNT(*) as underlings, d.dept_id FROM Employee e INNER JOIN Manages m ON e.employee_id = m.employee_id INNER JOIN Works_in w ON m.dept_id = w.dept_id INNER JOIN Department d ON w.dept_id = d.dept_id GROUP BY e.employee_id, d.dept_id HAVING underlings > 5 What i need to add to this query is which employee id manages the department with the count of emplyees greater than 5 Link to comment https://forums.phpfreaks.com/topic/77761-solved-struggling-with-query-select-all-employee_ids-who-manages-a-department-with-more/#findComment-393908 Share on other sites More sharing options...
AdRock Posted November 18, 2007 Author Share Posted November 18, 2007 I tried what i thought would work but i get too many values on line 5 This is what i tried SELECT Employee.ssn FROM Employee INNER JOIN Manages on Employee.employye_id = Manages.employee_id where Employee.employee_id= (SELECT e.employee_id, COUNT(*) as underlings, d.dept_id FROM Employee e INNER JOIN Manages m ON e.employee_id = m.employee_id INNER JOIN Works_in w ON m.dept_id = w.dept_id INNER JOIN Department d ON w.dept_id = d.dept_id GROUP BY e.employee_id, d.dept_id HAVING underlings > 5) Link to comment https://forums.phpfreaks.com/topic/77761-solved-struggling-with-query-select-all-employee_ids-who-manages-a-department-with-more/#findComment-393916 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.