| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help using hierarchical query CONNECT BY clause
On Tue, 1 Feb 2005 18:38:37 -0000, "Hexathioorthooxalate"
<ruler_at_removemetoemail.clara.co.uk> wrote:
>Okay, this is not for the feint harted and I've written a lot.
>
>Issue: I can't formulate SQL for a hierarchical statement using Oracles
>"CONNECT BY" clause
>Oracle Version: 10g
>Background: Working with an employee hierarchy example for simplicity. The
>real system reflects a laboratory sample tracking system, lots of things
>happens to one sample, and then lots of things to each of these samples, and
>then etc. The employee name is a sample name, the job title is a reagent
>batch. An existing (large) system is already place. I require readonly
>in-house mining of new features with absolute minimum of changes to existing
>schema. The existing system was developed by a number of authors, including
>the poster, and uses Oracle hierarchical queries to address many data
>queries. Because of the nature and structure of the existing data, it is
>also being used here.
>
>Here is the contrived employee hierarchy scenario that represents the more
>complex system above.
>
>
>Company Hierarchy
>=========================
>Peter "President", no boss
>Luke "Vice President", Peter is his boss
>John "Vice president" Peter is his boss
>Mary "Managing Director European Operations", Peter is her boss
>James "Special Projects Director", Peter is his boss
>Mark "Sales Director", Luke is his boss
>Tim "Marketing Director", Luke is his boss
>Johan "HR Director", Luke is his boss
>Mike "Sales Director", John is his boss
>Stephen "Sales Director", Mary is her boss
>Harry "HR Assistant", Johan is his boss
>Michelle "HR Assistant", Johan is her boss
>
>There are many many other people and job titles too. I have included the
>setup SQL below to help with copy/paste to get the same data for development
>on your machines.
>
>CREATE TABLE employees(empName VARCHAR2(15),empJobTitle VARCHAR2(40),empID
>NUMBER PRIMARY KEY,bossID NUMBER);
>
>INSERT INTO employees VALUES('Peter','President',1,NULL);
>INSERT INTO employees VALUES('Luke','Vice President',2,1);
>INSERT INTO employees VALUES('John','Vice President',3,1);
>INSERT INTO employees VALUES('Mary','Managing Director European
>Operations',4,1);
>INSERT INTO employees VALUES('James','Special Projects Director',5,1);
>INSERT INTO employees VALUES('Mark','Sales Director',6,2);
>INSERT INTO employees VALUES('Tim','Marketing Director',7,2);
>INSERT INTO employees VALUES('Johan','HR Director',8,2);
>INSERT INTO employees VALUES('Mike','Sales Director',9,3);
>INSERT INTO employees VALUES('Stephen','Sales Director',10,4);
>INSERT INTO employees VALUES('Harry','HR Assistant',11,8);
>INSERT INTO employees VALUES('Michelle','HR Assistant',12,8);
Yay, sample data.
>What I want is SQL to retrieve the names of the people in the organisation
>hierarchy that have as a job title "Sales Director" however their boss must
>have a job title of "Vice President" and their boss must have the job title
>of "President". And I want these job titles to be configurable too, so that
>I can change them on-the-fly and perform different queries.
>So my question is what SQL do I need to write to retrieve the names of
>employees that work for people with these job titles in this order
>"President", "Vice President", "Sales Director", IE ONLY THE EMPLOYEES Mike
>and Mark in the example above (BUT NOT Stephen, his bosses job title is
>"Managing Director European Operations" and not "Vice President").
OK, step one: find those people whose job title is VP and whos boss is Pres:
SQL> select e2.*
2 from employees e1
3 join employees e2
4 on (e2.bossid = e1.empid) 5 where e1.empjobtitle = 'President' 6 and e2.empjobtitle = 'Vice President'; EMPNAME EMPJOBTITLE EMPID BOSSID --------------- ---------------------------------------- ---------- ---------- Luke Vice President 2 1 John Vice President 3 1
Step two: find reports of those VPs whose job title is Sales Director:
SQL> select *
2 from employees
3 where empjobtitle = 'Sales Director'
4 connect by prior empid = bossid
5 start with empid in (
6 select e2.empid 7 from employees e1 8 join employees e2 9 on (e2.bossid = e1.empid) 10 where e1.empjobtitle = 'President' 11 and e2.empjobtitle = 'Vice President'12 );
EMPNAME EMPJOBTITLE EMPID BOSSID --------------- ---------------------------------------- ---------- ---------- Mark Sales Director 6 2 Mike Sales Director 9 3
Since it only goes down one level in your data then the connect by is superfluous here, you could have just done a three-way join.
SQL> select e3.*
2 from employees e1
3 join employees e2
4 on (e2.bossid = e1.empid)
5 join employees e3
6 on (e3.bossid = e2.empid) 7 where e1.empjobtitle = 'President' 8 and e2.empjobtitle = 'Vice President' 9 and e3.empjobtitle = 'Sales Director'; EMPNAME EMPJOBTITLE EMPID BOSSID --------------- ---------------------------------------- ---------- ---------- Mark Sales Director 6 2 Mike Sales Director 9 3
If you had more Sales Directors further down the hierarchy, or Sales Directors had people reporting to them then connect by might come into actual use.
e.g.
SQL> INSERT INTO employees VALUES('Postlethwaite','Sales Director',13,6);
1 row inserted
SQL> select lpad('-',level-1,'-')||e.empname empname, e.empjobtitle, e.empid
2 from employees e
3 where empjobtitle = 'Sales Director'
4 connect by prior empid = bossid
5 start with empid in (
6 select e2.empid 7 from employees e1 8 join employees e2 9 on (e2.bossid = e1.empid) 10 where e1.empjobtitle = 'President' 11 and e2.empjobtitle = 'Vice President'12 );
EMPNAME EMPJOBTITLE EMPID -------------------- ---------------------------------------- ---------- -Mark Sales Director 6 --Postlethwaite Sales Director 13 -Mike Sales Director 9
-- Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk> <http://www.andyhsoftware.co.uk/space> Space: disk usage analysis toolReceived on Tue Feb 01 2005 - 16:10:55 CST
 |
 |