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.
JOIN and a temporary table [Accepted]Algorithm
\nFirst, we can get the Id of the manager having more than 5 direct reports just using this ManagerId column.
\nThen, we can get the name of this manager by join that table with the Employee table.
\nMySQL
\nSELECT\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