Travesring data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Travesring data

Author

Topic

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-09-22 : 09:37:33

Let me start of by saying that this is not very good design and cannot currently be changed. The original data architect has left for better opportunities )heaven help his new employer!). Requirements gathering is taking place for a major rewrite of the system and cannot come a moment to soon. This is an access application that was ported to SQL Server 2K sp4 several years back. It's been tweaked and bandaged to no end and it's expiration date should have been Dec 31, 1999 - IMO. Anyway this is what I have and am looking for any solution suggestions (besides a bomb).

So, for the short term:
I have two tables which obviously contain many more columns, but for simplicity's sake these are the key fields for this process.

CREATE TABLE [EmployeeTC] (
[EE Employee Number] [float] NOT NULL ,
[EE First Name] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EE Last Name] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[managerID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [ManagerDataTC] (
[Mgr Id] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Mgr FirstName] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mgr LastName] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

It contains some data like:

insert into EmployeeTC values(1, 'John', 'Doe', '12345')
insert into EmployeeTC values(2,'Jane', 'Doe', '12345')
insert into EmployeeTC values(3, 'Tom', 'Jones', '12345')
insert into EmployeeTC values(4, 'Michael', 'Jones', '12345')
insert into EmployeeTC values(5, 'George', 'Jones', '12346')
insert into EmployeeTC values(6, 'Peggy', 'Smith', '12347')
insert into EmployeeTC values(7, 'Rita', 'Moses', '')

insert into ManagerDataTC values('12345', 'George', 'Jones')
insert into ManagerDataTC values('12346', 'Peggy', 'Smith')
insert into ManagerDataTC values('12347', 'Rita', 'Moses')
insert into ManagerDataTC values('12348', 'Iva', 'Nobody')

I'm trying to get by hands around how to do this and am stumped. What I would like to do, for an employee, get the manager (no problem there) but also get that manager's manager and so forth, all the way up the tree. The developer is coding this up in Java as a loop until no other manager information is found. I brilliantly thought I could do this with SQL but my results are nil after I reach the first manager. I could do basically the same looping rountine within SQL but I'm certain there is a better way. Anyone have any suggestions? TIA.

FYI - There can be upto n levels of managers.

Terry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 09:43:34

this should give you a start:-
http://support.microsoft.com/kb/248915

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-22 : 10:23:55

quote:
Originally posted by visakh16

this should give you a start:-
http://support.microsoft.com/kb/248915

Visakh,
I think you should find different reference for this problem. Here is an exerpt from a post where this kb article was recomended:

from: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109492

quote:
Originally posted by TG

That KB artical uses the technique that does one iteration per value rather than what I suggested as one iteration per level of nesting.

The code in that link makes 19 recursive queries. This code (based on the same sample data) uses 5:

Another similar example of a better SS2K method for hierachies:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63042

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 11:01:22

quote:
Originally posted by TG

quote:
Originally posted by visakh16

this should give you a start:-
http://support.microsoft.com/kb/248915

Visakh,
I think you should find different reference for this problem. Here is an exerpt from a post where this kb article was recomended:

from: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109492
quote:
Originally posted by TG

That KB artical uses the technique that does one iteration per value rather than what I suggested as one iteration per level of nesting.

The code in that link makes 19 recursive queries. This code (based on the same sample data) uses 5:

Another similar example of a better SS2K method for hierachies:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63042

Be One with the Optimizer
TG

ok. i've updated my bookmark

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-09-22 : 14:29:16

OK, this is odd - I subscribed to my topic but never received an email from any of the responses. Anyway, several hours later, I got it to work on my own using a While exists loop and then inserting into a table. I had to simply walk away from it for a while to refocus and see the bigger picture. As always, you all provided accurate help and spot on references that are nearly identical to my ultimate solution. I appreciate all the replies.

Terry

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-10-07 : 09:27:18

Got it to work, traversing down the tree, using a while exists and a temp table, all wrapped neatly in a stored proc. I then use my temp table further to produce a list of all employees for anyone in the list. I also have another routine the will go up the tree. What a pain in the A@@ but I loved the challenge it represented! I just keep saying to myself, "the rewrite is coming, the rewrite is coming...."

DECLARE @inputManagerId int --this is the manager requesting the data
DECLARE @tempManagerId int
select [ManagerID], 'N' as processed into #TCManagers from [ManagerDataTC] where [Mgr ID] = @inputManagerID --Top level manager

while exists (select Managerid from #TCManagers where Processed = 'N')

Begin
SELECT @tempManagerId = ManagerID from #TCManagers where Processed = 'N'
update #TCManagers set Processed = 'Y' where ManagerID = @tempManagerId
insert into #TCManagers select m.[ManagerID], 'N' from ManagerTC m inner join
EmployeeTC e on m.[Mgr ID] = [EE Mgr ID]
where e.ManagerID = @tempManagerID

end

Terry

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 10:39:57

Good lucks!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-10-07 : 11:31:56

Terry, I think you will find this similar to what you came up with, but a little more streamlined and more easily adaptable to other traversing challenges: http://sqlblindman.googlepages.com/returningchildrecords
Plus, if you are using SQL Server 2005, then the proper way to do this is with Common Table Expressions.

Boycotted Beijing Olympics 2008

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-10-07 : 13:50:25

Where were you a week ago?? :-) Actually, this is now bookmarked for future reference, thank you very much! We're still on 2000 with an upgrade ocurring 1st quarter next year, at the same time as the rewrite of the application. I knew about CTEs as I'm reading anything and everything I can that appears to potentially make my life easier. I've seen some great stuff. I appreciate the links and all suggestions.

Terry

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-10-07 : 14:15:56

Dude! I was right here! Didn't you see me?
Tonight, however, I will be lurking your basement.

Boycotted Beijing Olympics 2008

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-10-07 : 14:18:42

Good thing my house is built on a slab!

Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources