On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Saturday, June 7, 2008

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

No comments:

Post a Comment