Self-Join

The employee records have two different employee numbers -- one for the individual and one for their boss. The people in department 10 are listed below. As shown, Clark reports to 7839, who we can see is King. Miller reports to 7782, who is Clark.


To do this in SQL, you need to join the table to itself. It is defined twice in the query, and given different aliases by which we can address each version of the table.

Select e1.EmpNo,
       e1.EName,
       e1.Mgr  ,
       e2.EName As Manager
  From Emp e1
  Join Emp E2
    On e1.Mgr = e2.EmpNo
 Where e1.DeptNo = 10
 Order By EmpNo;

 EMPNO ENAME             MGR MANAGER
------ ---------- ---------- ---------
  7782 CLARK            7839 KING
  7934 MILLER           7782 CLARK