SELF JOIN query

[BIJOYSR]

I have a table having user_id, User_name, Manager_id where
user_id is self joined to manager_id. user_id is primary key and manager_id is foriegn key refering user_id.

Can any one tell me how can i make a query that can return
the hierarchy of all the managers of a user_id entered.
E.g. 2 is manager of 1, 3 is manager of 2, 4 is manager of 3, 5 is manager of 7.
when i give 1 - i want the result as
2
3
4

 

[laffreuxthomas]

I think it's impossible with SQL only. You can do that with a program.

Thomas ---
A free GUI for your database:

http://www.sashipamelba.com

 

[topher0303]

How about a stored procedure. I don't know your data structure so I will make one up for the example:

Table: Employees
EmpId int not null [primary key]
ManagerId int
EmpName varchar(50) not null

You could use a sproc that looked something like this:
---------------
create procedure usp_GetHierarchy
@emp int
as
set nocount on

declare @h TABLE (ord int, empid int not null, managerid int, empname varchar(50))
declare @ord int

set @ord=1
insert into @h select @ord, empid, managerid, empname from employees where empid=@emp
while @emp is not null
begin
set @ord = @ord + 1
select @emp = managerid from employees where empid = @emp

-- the next three lines prevents an infinite loop in the event of circular reference
-- these three lines could be removed if an triggers existed to prevent circular reference
if exists(select empid from @h where empid = @emp)
break
else
insert into @h select @ord, empid, managerid, empname from employees where empid=@emp
end

select * from @h order by ord desc

set nocount off
-- end of sproc

Chris.

 

[topher0303]

Sorry, I thought I was in the SQL Server forum. What brand of database are you using. The sproc I created is for SQL Server. If it is oracle, it has a way to do this in a single SQL statement.

Chris.

 

[laffreuxthomas]

Hi Chris,

I'm interested to see how to do the problem in a single SQL statement for Oracle. Can you show me on your table Employees ?

Thomas ---
A free GUI for your database:

http://www.sashipamelba.com

 

[topher0303]

This would work its way up the hierarchy:

SELECT level, emp_id, manager_id, emp_name
FROM employees
START WITH emp_id = 6
CONNECT BY emp_id = PRIOR manager_id

START WITH tells it where to start in the hierarchy
CONNECT BY signifies the referencing columns
PRIOR indicates which is the parent in the reference
LEVEL is a column that is not defined in the table. It creates a column in the resultset that indicates the level in the hierarchy of the record.

Putting PRIOR in front of emp_id would cause the it to work it's way down the hierarchy:

SELECT level, emp_id, manager_id, emp_name
FROM employees
START WITH emp_id = 6
CONNECT BY PRIOR emp_id = manager_id


Chris.

 

[topher0303]

BTW, my previous message containing

SELECT level, emp_id, manager_id, emp_name
FROM employees
START WITH emp_id = 6
CONNECT BY emp_id = PRIOR manager_id

was a response to laffreuxthomas question. It can be used only in Oracle. I just wanted to clarify that to keep from confusing anyone.

Chris.

 

[laffreuxthomas]

thanks a lot ! ---
A free GUI for your database:

http://www.sashipamelba.com