Link to home
Start Free TrialLog in
Avatar of AlHal2
AlHal2Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Retrieve data for an employee, their immediate reports and hierarchy up to the CEO

Using this table as an example, I'd like to be able to enter an employee ID.  The resulting SQL should give me the employee's direct reports.  It should also give me the employee's chain of command up to the President.  I'm using SQL 2012.

create table ##EMPLOYEES
    (EmpID    char(4)         unique Not null,
     Ename    varchar(10),
     Job      varchar(9),
     MGR      char(4),
     Hiredate date,
     Salary   decimal(7,2),
     Comm     decimal(7,2),
     DeptNo   char(2)         not null,
         Primary key(EmpID),
         --Foreign key(DeptNo) REFERENCES DEPARTMENTS(DeptNo)
		 );
--The following is my INSERT script:

insert into ##EMPLOYEES values (7839,'King','President',null,'17-Nov-11',5000,null,10);
insert into ##EMPLOYEES values (7698,'Blake','Manager',7839,'01-May-11',2850,null,30);
insert into ##EMPLOYEES values (7782,'Clark','Manager',7839,'02-Jun-11',2450,null,10);
insert into ##EMPLOYEES values (7566,'Jones','Manager',7839,'02-Apr-11',2975,null,20);
insert into ##EMPLOYEES values (7654,'Martin','Salesman',7698,'28-Feb-12',1250,1400,30);
insert into ##EMPLOYEES values (7499,'Allen','Salesman',7698,'20-Feb-11',1600,300,30);
insert into ##EMPLOYEES values (7844,'Turner','Salesman',7698,'08-Sep-11',1500,0,30);
insert into ##EMPLOYEES values (7900,'James','Clerk',7698,'22-Feb-12',950,null,30);
insert into ##EMPLOYEES values (7521,'Ward','Salesman',7698,'22-Feb-12',1250,500,30);
insert into ##EMPLOYEES values (7902,'Ford','Analyst',7566,'03-Dec-11',3000,null,20);
insert into ##EMPLOYEES values (7369,'Smith','Clerk',7902,'17-Dec-10',800,null,20);
insert into ##EMPLOYEES values (7788,'Scott','Analyst',7566,'09-Dec-12',3000,null,20);
insert into ##EMPLOYEES values (7876,'Adams','Clerk',7788,'12-Jan-10',1100,null,20);
insert into ##EMPLOYEES values (7934,'Miller','Clerk',7782,'23-Jan-12',1300,null,10);

Open in new window

Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

your table isn't complete in the sense that it didn't tell us how the Job should be defined.

we can't see a hierarchy from the data you have provided above.
Avatar of AlHal2

ASKER

In the above, King's ID is 7839.  He is the president of the company and is the first row to be inserted.
The next 3 rows are managers.  You'll see the 4th column for all these managers is 7839 ie they report to the president.
Does this clarify?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AlHal2

ASKER

Thanks both.