Gani Ganesh Gani Ganesh - 1 year ago 54
SQL Question

small tricky query on self join

I have a table EMP with columns as below:

create table emp(
empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr_id number(4,0),
sal number(7,2),
deptno number(2,0));

I want to list all employees' names along with their manager names, including those who do not have a manager. For those employees, their manager's name should be displayed as 'BOSS'.

Answer Source

The following query should work:

select e.ename, (case when m.ename is null then 'BOSS' else m.ename end) as mgrName 
from emp e
left join emp m on m.empno = e.mgr_id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download