[SOLVED] Struggling with query select all employee_ids who manages a department with more

[AdRock]

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)

[Barand]

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

[AdRock]

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

[AdRock]

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)