Output the names of bosses who manage multiple employees-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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