Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SELF JOIN query

Status
Not open for further replies.

BIJOYSR

MIS
Joined
Jan 10, 2003
Messages
1
Location
IN
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
 
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.
 
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.
 
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:
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top