Organizational Chart - Bill of Materials [BOM] with MS SQL Server
- They are the perfect examples of self joins.
- The same logic applies to both the organization chart and to bill of materials [BOM].
The goal is to get:
EMPLOYEE ID EMPLOYEE NAME SUPERVISOR ID SUPERVISOR NAME ------------------------------------------------------------------- 1 ABC 2 AXYZ 1 ABC 3 SDER 1 ABC 4 KJHYU 2 AXYZ ...
Summary
It's very difficult to predict how many levels will exist. How many levels of supervisors will there be? What happens when you will need to add or delete another level in the Bill of Material.
By using a self-join, it's unlimited [almost.]
Data used
select EMPNO,EMPNAME,MGRNO from org; EMPNO EMPNAME MGRNO ------ ---------------- ------ AA001 HAAS NULL AA002 THOMPSON AA001 AA003 KWAN AA001 AA004 GEYER AA001 AA005 STERN AA003 AA006 PULASKI AA003 AA007 HENDERSON AA001 AA008 SPENSER AA003 AA009 LUCCHESSI AA003 AA010 QUINTANA AA003 (10 row(s) affected)
Note that it returns: 10 rows
Self link
select t1.empno Emp, t1.empname 'Employee Name' , t1.mgrno Mgr, t2.empname Manager from org t1, org t2 where t1.mgrno = t2.empno order by t1.mgrno; Emp Employee Name Mgr Manager ------ ---------------- ------ ---------------- AA002 THOMPSON AA001 HAAS AA003 KWAN AA001 HAAS AA004 GEYER AA001 HAAS AA007 HENDERSON AA001 HAAS AA008 SPENSER AA003 KWAN AA009 LUCCHESSI AA003 KWAN AA010 QUINTANA AA003 KWAN AA005 STERN AA003 KWAN AA006 PULASKI AA003 KWAN (9 row(s) affected)
Note that only 9 rows are displayed, it does not display the Boss because the Boss does not have a manager.
Self Link Outer Join
select t1.empno Emp, t1.empname 'Employee Name' , t1.mgrno Mgr, t2.empname Manager from org t1 left outer join org t2 on t1.mgrno = t2.empno order by t1.mgrno; Emp Employee Name Mgr Manager ------ ---------------- ------ ---------------- AA001 HAAS NULL NULL AA002 THOMPSON AA001 HAAS AA003 KWAN AA001 HAAS AA004 GEYER AA001 HAAS AA007 HENDERSON AA001 HAAS AA008 SPENSER AA003 KWAN AA009 LUCCHESSI AA003 KWAN AA010 QUINTANA AA003 KWAN AA005 STERN AA003 KWAN AA006 PULASKI AA003 KWAN (10 row(s) affected)
Note that it returns: 10 rows. The left outer join means to include all the rows from the left table t1, including the ones that do not have a link in the right table t2.
Improved Organizational Chart 1
select t1.empno Emp, t1.empname 'Employee Name' , t1.mgrno Mgr, t2.empname Manager from org t1 left outer join org t2 on t1.mgrno = t2.empno order by t1.empno; Emp Employee Name Mgr Manager ------ ---------------- ------ ---------------- AA001 HAAS NULL NULL AA002 THOMPSON AA001 HAAS AA003 KWAN AA001 HAAS AA004 GEYER AA001 HAAS AA005 STERN AA003 KWAN AA006 PULASKI AA003 KWAN AA007 HENDERSON AA001 HAAS AA008 SPENSER AA003 KWAN AA009 LUCCHESSI AA003 KWAN AA010 QUINTANA AA003 KWAN (10 row(s) affected)
This organizational is sorted by the employee number, as long as the Big Boss is entered first, then it will be sorted as an logical organization chart. The order by will determine how the organizational chart or the bill of material is displayed.
Improved Organizational Chart 2
select t1.empno Emp, t1.empname 'Employee Name' , t1.mgrno Mgr, t2.empname Manager from org t1 left outer join org t2 on t1.mgrno = t2.empno order by t1.empname; Emp Employee Name Mgr Manager ------ ---------------- ------ ---------------- AA004 GEYER AA001 HAAS AA001 HAAS NULL NULL AA007 HENDERSON AA001 HAAS AA003 KWAN AA001 HAAS AA009 LUCCHESSI AA003 KWAN AA006 PULASKI AA003 KWAN AA010 QUINTANA AA003 KWAN AA008 SPENSER AA003 KWAN AA005 STERN AA003 KWAN AA002 THOMPSON AA001 HAAS (10 row(s) affected)
This organizational is sorted by the employee name. The order by will determine how the organizational chart or the bill of material is displayed.