I have this table employee3_3:
| 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 | A | 101 |
Here John is the manager, that’s why he doesn’t have a managerId value. But to prove he is the manager, I’m asked to write a query to find managers with at least five direct employees.
I tried this:
WITH cte AS
(
SELECT
name, managerid,
(SELECT COUNT(b.managerid)
FROM employee3_3 AS b) AS result
FROM
employee3_3
)
SELECT employee3_3.name
FROM employee3_3
FULL JOIN cte ON employee3_3.name = cte.name
WHERE result = 5
AND cte.managerid = NULL
For some reason, it doesn’t return any result.
I also tried this:
WITH cte AS
(
SELECT
managerid, COUNT(managerid) AS number_of_employees
FROM
employee3_3
GROUP BY
managerid, managerid
)
SELECT name
FROM employee3_3 AS e
JOIN cte ON e.managerid = cte.managerid
WHERE cte.number_of_employees <= 5
I’m not sure if is my approach that is wrong and I’m not in the right path, or if I’m not using the right functions.
>Solution :
select e.*
from employee3_3 e
inner join
(select managerId
from employee3_3
group by managerId having count(*) >= 5
) m on e.Id = m.managerId;
PS: If a manager is meant to be her\his managerId is null then you should add that criteria (then only top level managers would count as manager)