Recursive Query

[RohanWest]

Hi there

I have a table that look like this it contains user names

User
+--------------
Paul
Dan
Peter
Simon
John

Each user can be a manager of a different user. I have written a query that shows who manages who

User Manager
+--------------+--------
Paul Dan
Dan Peter
Peter John
Simon Peter
John John

Is it possible to create a SQL statement that will show a list of managers that can manage a user. A list of user above the requested user.

For example if i used "Paul" as a parameter then i would get a list like this

User
+--------------
Dan
Peter
John

Any help would be much appreciated.

Thanks Rohan

 

[manufcs]

Hi,


SELECT MANAGER, USER FROM USER_MANAGER GROUP BY MANAGER

SELECT USER FROM USER_MANAGER WHERE MANAGER = 'PAUL'

Hope I have solved it right.

Manu

 

[RohanWest]

The thing is the manager and user table is the same table
there has to be an outer join some where

here are the fields of the table

UserId
ManagerID
UserName

The manager ID points to another UserID

 

[rt63]

I do not think it can be done using a simple query unless you put a restriction on the number of levels a user/manager relationship can go , i.e. , u1 managed by u2 managed by u3 and so on - it can be any level. You can write a small batch/proc to get this done


select distinct manager
into #temp
from user_manager
where user = 'paul'

while @@rowcount > 0
begin
insert into #temp
select distinct manager
from user_manager
where manager not in (select manager from #temp)
and manager in (select user from user_manager)
end

select manager from #temp


RT

 

[RohanWest]

Thanks, i have just been thinking about it and your right,

you would have to specify the number of levels in order to know how many joins to create.

I think that i will create a stored procedure and use similar code as you said...

Thanks

Rohan

 

[RohanWest]

that works great cheers!

 

[RohanWest]

Hi back again,

That code you gave works great if i need to know all the managers. I only need to know about managers that are above that user.

Any ideas?

Rohan

 

[rt63]

Sorry. A little mistake - try this


select distinct manager
into #temp
from user_manager
where user = 'paul'

while @@rowcount > 0
begin
insert into #temp
select distinct manager
from user_manager
where user in (select manager from #temp)
and manager not in (select manager from #temp)
end

select manager from #temp


RT

 

[tlbroadbent]

If you are interested in recurion is SQL, check out the following links.

The Zen of Recursion

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/recursion.asp

Recursive procedure

http://www.sqlteam.com/ItemPrint.asp?ItemID=1353

Using T-SQL to Generate a Resultset in Tree Form

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=19663

More Trees & Hierarchies in SQL

http://www.sqlteam.com/item.asp?ItemID=8866

Recursion Function

http://www.winscriptingsolutions.com/Articles/Index.cfm?ArticleID=16197

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.