## 570. Managers with at Least 5 Direct Reports

The `Employee` table 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 the `Employee` table, 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.

b'
\n\n

## Solution

\n
\n

#### Approach: Using `JOIN` and a temporary table [Accepted]

\n

Algorithm

\n

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

\n

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

\n

MySQL

\n
`SELECT\n    Name\nFROM\n    Employee AS t1 JOIN\n    (SELECT\n        ManagerId\n    FROM\n        Employee\n    GROUP BY ManagerId\n    HAVING COUNT(ManagerId) >= 5) AS t2\n    ON t1.Id = t2.ManagerId\n;\n`
\n
'