Output the names of bosses who manage multiple employees

Rjck11 0
Viewed 24

I have a database of personnel which has an EMPLOYEE_ID, EMPLOYEE_NAME and BOSS_ID. I'm trying to display the employee_name of all bosses who manage 3 or more people. The following query provides me with all the right BOSS_ID's.

SELECT BOSS_ID FROM PERSONNEL
GROUP BY BOSS_ID
HAVING COUNT(BOSS_ID) >= 3

However I wish to only display the EMPLOYEE_NAME of the bosses, not their BOSS_ID. I tried this, but it produced no names at all.

SELECT EMPLOYEE_NAME FROM PERSONNEL
GROUP BY EMPLOYEE_NAME
HAVING COUNT(BOSS_ID) >= 3

Any help would be greatly appreciated.

sql
3 Answers

You can do a JOIN on the same table to obtain the boss's name.

SELECT p.Employee_name, b.Employee_name as "Boss Name"
FROM personnel p
INNER JOIN personnel b ON b.Employee_ID = p.Boss_ID
Ken White0

To get the Manager Name your can do Self Join.

In the below example to get the Manager name you need to get it from P2 Reference.

SELECT DISTINCT P2.EMPLOYEE_NAME AS ManagerName
FROM PERSONNEL P1
JOIN PERSONNEL P2 ON P1.BOSS_ID = P2.EMPLOYEE_ID
GROUP BY P2.EMPLOYEE_NAME
HAVING COUNT(P1.BOSS_ID) >= 3
userpiG90

you can try using a subquery.

SELECT Employee_name
FROM PERSONNEL
where BOSS_ID IN (

SELECT BOSS_ID FROM PERSONNEL
GROUP BY BOSS_ID
HAVING COUNT(BOSS_ID) >= 3
)

db fiddle

Jocohan0
Related