TheEmployeetable holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+------+----------+-----------+----------+
|Id    |Name       |Department |ManagerId |
+------+----------+-----------+----------+
|101   |John       |A           |null      |
|102   |Dan       |A           |101       |
|103   |James       |A           |101       |
|104   |Amy       |A           |101       |
|105   |Anne       |A           |101       |
|106   |Ron       |B           |101       |
+------+----------+-----------+----------+

Given theEmployeetable, write a SQL query that finds out managers with at least 5 direct report. For the above table, your SQL query should return:

+-------+
| Name  |
+-------+
| John  |
+-------+

Note:
No one would report to himself.

Solution

Approach: UsingJOINand a temporary table [Accepted]

Algorithm

First, we can get the Id of the manager having more than 5 direct reports just using this_ManagerId_column.

Then, we can get the name of this manager by join that table with theEmployeetable.

MySQL

select Name 
from Employee A join (
select ManagerId
from Employee
GROUP BY ManagerId
HAVING count(ManagerId) >= 5) as B
on A.Id = B.ManagerId;