How do I Join a table to itself recursively ?

[Amit Jain]

Hi !

I have an EMPLOYEE table as below.

Table - EMPLOYEE
Id (Int) (P)
EmployeeName Varchar(30)
Parent_Id (Int) (Reference to EMPLOYEE.Id)

I want to retrieve all the Employees of any given Manager.
For eg: Given a CEO, I want to retrieve all the Managers and Sub Managers and Sub Sub Managers ... under it.

This is a typical example of a Self join Table. How can I do that.

Can anyone help ??

Regards.

[Phil McCormack]

don't know whether this is what you want :-

create table employee (
Id Int,
EmployeeName Varchar(30),
Parent_Id Int)
go
insert into employee select 1,'CEO',1
insert into employee select 2,'FD',1
insert into employee select 3,'TD',1
insert into employee select 4,'Prog1',3
insert into employee select 5,'Prog2',3
insert into employee select 6,'Prog3',3
insert into employee select 7,'clerk1',2
insert into employee select 8,'clerk2',2
insert into employee select 9,'clerk3',2


Now the SQL Select

declare @intManager int
select @intManager=3

select a.*
from employee a
where (a.parent_id in
(select b.id from employee b
where b.id=@intManager
) or
a.id=@intManager)

RESULTS
Id EmployeeName Parent_Id
----------- ------------------------------ -----------
3 TD 1
4 Prog1 3
5 Prog2 3
6 Prog3 3



------------
Amit Jain at 2/12/01 6:26:05 AM

Hi !

I have an EMPLOYEE table as below.

Table - EMPLOYEE
Id (Int) (P)
EmployeeName Varchar(30)
Parent_Id (Int) (Reference to EMPLOYEE.Id)

I want to retrieve all the Employees of any given Manager.
For eg: Given a CEO, I want to retrieve all the Managers and Sub Managers and Sub Sub Managers ... under it.

This is a typical example of a Self join Table. How can I do that.

Can anyone help ??

Regards.

[Nilesh]

This will give upto second level not Nth level.
so if you pass CEO it it will not print all employee.
use cursor & temp table etc to get all list (uniq again)

Nilesh



------------
Phil McCormack at 2/12/01 6:53:12 AM

don't know whether this is what you want :-

create table employee (
Id Int,
EmployeeName Varchar(30),
Parent_Id Int)
go
insert into employee select 1,'CEO',1
insert into employee select 2,'FD',1
insert into employee select 3,'TD',1
insert into employee select 4,'Prog1',3
insert into employee select 5,'Prog2',3
insert into employee select 6,'Prog3',3
insert into employee select 7,'clerk1',2
insert into employee select 8,'clerk2',2
insert into employee select 9,'clerk3',2


Now the SQL Select

declare @intManager int
select @intManager=3

select a.*
from employee a
where (a.parent_id in
(select b.id from employee b
where b.id=@intManager
) or
a.id=@intManager)

RESULTS
Id EmployeeName Parent_Id
----------- ------------------------------ -----------
3 TD 1
4 Prog1 3
5 Prog2 3
6 Prog3 3



------------
Amit Jain at 2/12/01 6:26:05 AM

Hi !

I have an EMPLOYEE table as below.

Table - EMPLOYEE
Id (Int) (P)
EmployeeName Varchar(30)
Parent_Id (Int) (Reference to EMPLOYEE.Id)

I want to retrieve all the Employees of any given Manager.
For eg: Given a CEO, I want to retrieve all the Managers and Sub Managers and Sub Sub Managers ... under it.

This is a typical example of a Self join Table. How can I do that.

Can anyone help ??

Regards.

[Amit Jain]

Is there no direct way of getting it. A procedure to retrieve a tree for a 5 Level itself will take very long. and the procedure will be very tedious to write.


------------
Nilesh at 2/12/01 2:05:19 PM

This will give upto second level not Nth level.
so if you pass CEO it it will not print all employee.
use cursor & temp table etc to get all list (uniq again)

Nilesh



------------
Phil McCormack at 2/12/01 6:53:12 AM

don't know whether this is what you want :-

create table employee (
Id Int,
EmployeeName Varchar(30),
Parent_Id Int)
go
insert into employee select 1,'CEO',1
insert into employee select 2,'FD',1
insert into employee select 3,'TD',1
insert into employee select 4,'Prog1',3
insert into employee select 5,'Prog2',3
insert into employee select 6,'Prog3',3
insert into employee select 7,'clerk1',2
insert into employee select 8,'clerk2',2
insert into employee select 9,'clerk3',2


Now the SQL Select

declare @intManager int
select @intManager=3

select a.*
from employee a
where (a.parent_id in
(select b.id from employee b
where b.id=@intManager
) or
a.id=@intManager)

RESULTS
Id EmployeeName Parent_Id
----------- ------------------------------ -----------
3 TD 1
4 Prog1 3
5 Prog2 3
6 Prog3 3



------------
Amit Jain at 2/12/01 6:26:05 AM

Hi !

I have an EMPLOYEE table as below.

Table - EMPLOYEE
Id (Int) (P)
EmployeeName Varchar(30)
Parent_Id (Int) (Reference to EMPLOYEE.Id)

I want to retrieve all the Employees of any given Manager.
For eg: Given a CEO, I want to retrieve all the Managers and Sub Managers and Sub Sub Managers ... under it.

This is a typical example of a Self join Table. How can I do that.

Can anyone help ??

Regards.