Gani Ganesh Gani Ganesh - 3 months ago 20
SQL Question

small tricky query on self join in sql

i have one emp table which is having columns like below.
this table is default emp table in sql..

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));


so i want to print all employees names and their manager names as well as who do not have manager, for those employees their manger name should display 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