September 19, 2008 at 11:50 am
I have two tables which obviously contain many more columns, but for simplicity's sake. Running SQL2K sp4.
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 within a single select 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?
Edit: There can be upto n levels of managers
-- You can't be late until you show up.
September 19, 2008 at 5:51 pm
The way you database is built is a dead end.
An attempt to copy objects to tables is typical error of developers-beginners.
It will lead to overcomplicated and very pad performing code. Not to mention maintenance nightmare.
Databases have quite different rules for design.
Learn a little about data normalization.
Here is an example how it should be (it's just outline, but hope it will give you an idea):
CREATE TABLE [Person] (
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
[First Name] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last Name] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
UNIQUE ([First Name], [Last Name] )
-- This constraint is not right, different people with the same name exist, but I reference persons by names, so I used names as an identifier.
-- Replace it with whatever they in the organization use to uniquely identify a person
)
GO
CREATE TABLE [ManagementRelation] (
[Mgr PersonId] int NOT NULL,
[Empl PersonID] int NOT NULL,
[Hier Level] tinyint NOT NULL
)
GO
insert into Person
([First Name], [Last Name])
SELECT [First Name], [Last Name]
FROM (
select 'John' [First Name], 'Doe' [Last Name]
union all
select 'Jane' [First Name], 'Doe' [Last Name]
union all
select 'Tom' [First Name], 'Jones' [Last Name]
union all
select 'Michael' [First Name], 'Jones' [Last Name]
union all
select 'George' [First Name], 'Jones' [Last Name]
union all
select 'Peggy' [First Name], 'Smith' [Last Name]
union all
select 'Rita' [First Name], 'Moses' [Last Name]
union all
select 'Iva' [First Name], 'Nobody' [Last Name]
) DT
INSERT INTO [ManagementRelation] (
[Mgr PersonId] , [Empl PersonID], [Hier Level])
SELECT M.ID, E.ID, DT.[Level]
FROM (
select 'George' [Mgr First Name], 'Jones' [Mgr Last Name], 'John' [Emp First Name], 'Doe' [Emp Last Name], 1 [Level]
union all
select 'George', 'Jones', 'Jane', 'Doe', 1
union all
select 'George', 'Jones', 'Tom', 'Jones', 1
union all
select 'George' , 'Jones' , 'Michael' , 'Jones', 1
union all
select 'Peggy', 'Smith', 'George', 'Jones', 1
union all
select 'Rita', 'Moses', 'Peggy', 'Smith', 1
) DT
INNER JOIN dbo.Person M ON M.[First Name] = DT.[Mgr First Name] AND M.[Last Name] = DT.[Mgr Last Name]
INNER JOIN dbo.Person E ON E.[First Name] = DT.[Emp First Name] AND E.[Last Name] = DT.[Emp Last Name]
INSERT INTO [ManagementRelation] (
[Mgr PersonId] , [Empl PersonID], [Hier Level])
SELECT M.[Mgr PersonId], E.[Empl PersonID], 2
FROM [ManagementRelation] M
INNER JOIN [ManagementRelation] E ON M.[Empl PersonID] = E.[Mgr PersonId]
WHERE M.[Hier Level] = 1
INSERT INTO [ManagementRelation] (
[Mgr PersonId] , [Empl PersonID], [Hier Level])
SELECT M.[Mgr PersonId], E.[Empl PersonID], 3
FROM [ManagementRelation] M
INNER JOIN [ManagementRelation] E ON M.[Empl PersonID] = E.[Mgr PersonId]
WHERE M.[Hier Level] = 2
INSERT INTO [ManagementRelation] (
[Mgr PersonId] , [Empl PersonID], [Hier Level])
SELECT M.[Mgr PersonId], E.[Empl PersonID], 4
FROM [ManagementRelation] M
INNER JOIN [ManagementRelation] E ON M.[Empl PersonID] = E.[Mgr PersonId]
WHERE M.[Hier Level] = 3
SELECT E.[First Name] as [Emp First Name], E.[Last Name] AS [Emp Last Name],
M.[First Name] as [Mgr First Name], M.[Last Name] AS [Mgr Last Name],
[Hier Level] As [Manager Level]
FROM [ManagementRelation] R
INNER JOIN dbo.Person E ON E.ID = R.[Empl PersonID]
INNER JOIN dbo.Person M ON M.ID = R.[Mgr PersonId]
With this structure you need to "update everywhere" when some of employees has married and changed last name.
All managers are accessible easily and instantly.
If you add a new employee you just set a manager for him/her and add all managers of the manager with [Hier Level] = manager [Hier Level] + 1
Say, if you want to set up "Iva Nobody" as a manager for "Rita Moses" it's gonna look like this:
INSERT INTO [ManagementRelation] (
[Mgr PersonId] , [Empl PersonID], [Hier Level])
SELECT M.ID, E.ID, DT.[Level]
FROM (
select 'Iva' [Mgr First Name], 'Nobody' [Mgr Last Name], 'Rita' [Emp First Name], 'Moses' [Emp Last Name], 1 [Level]
) DT
INNER JOIN dbo.Person M ON M.[First Name] = DT.[Mgr First Name] AND M.[Last Name] = DT.[Mgr Last Name]
INNER JOIN dbo.Person E ON E.[First Name] = DT.[Emp First Name] AND E.[Last Name] = DT.[Emp Last Name]
-- And now you set Iva Nobody as a manager for all levels employees of Rita Moses.
-- With a single simple statement
INSERT INTO [ManagementRelation] (
[Mgr PersonId] , [Empl PersonID], [Hier Level])
SELECT M.ID, EE.[Empl PersonID], EE.[Hier Level] + 1
FROM (
select 'Iva' [Mgr First Name], 'Nobody' [Mgr Last Name], 'Rita' [Emp First Name], 'Moses' [Emp Last Name], 1 [Level]
) DT
INNER JOIN dbo.Person M ON M.[First Name] = DT.[Mgr First Name] AND M.[Last Name] = DT.[Mgr Last Name]
INNER JOIN dbo.Person E ON E.[First Name] = DT.[Emp First Name] AND E.[Last Name] = DT.[Emp Last Name]
INNER JOIN [ManagementRelation] EE ON E.ID = EE.[Mgr PersonId]
_____________
Code for TallyGenerator
September 19, 2008 at 7:03 pm
Unless the hierarchy changes every minute or two, there's no sense in recalculating the same thing over and over and over...
I gotta say this first... you need to take the guy or gal that designed this "system" out for a porkchop dinner... (s)he has violated virtually every rule of common sense and best coding practices that I can think of. Let's start with just the basic structure... managers are employees, too, and should not live in a separate table with yet another set of IDs. It doesn't make sense. Then, there's the naming convention where the same data is called something else just because it lives in a different table. I won't even get started on the datatypes and the related disparitys between like columns...
Heh... A "porkchop dinner", served up "Moden style", is where you take the person out to dinner, tie them to the chair so you have their full attention, and then "feed" them porkchops with "Best Practices" notes stapled to them... from point blank range... with a sling shot. 😛
Anyway... back to your problem. How often will the hiearchy change?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2008 at 6:52 am
If you think the design of this one is bad, you should see another system that is currently up for rewrite. Started in Access, ported to SQL backend but still using Access as the front end. We outgrew this little puppy years ago. No normalization, bandaged to no end, a total nightmare. The one I'm trying to assist on isn't quite as old but it looks like the handiwork of the same data "architect" (I use that term loosely) who is no longer here. Both systems were here long before I came on board six months ago. I'm simply trying to help them out (read - more bandaids) until the two are combined into one tight system (I will be involved with design!!!). Anyway, after that rant, hierarchy should rarely change. Any thoughts on a solution?
Also, the porkchop dinner would allow the genius to get off to easy IMO. I'm thinking something like tied to a anthill - naked - and slathered in honey! In reality, on the employee table, there are two manager IDs, one for the actual manager of the employee, and if the employee is a manager, their manager number as well. No documentation (of course!) so trying to determine relationships between tables is a major chore, hence the re-write. Can't come soon enough for me. I appreciate any and all suggestions for what I'm hoping is a short-term resolution. TIA.
-- You can't be late until you show up.
September 22, 2008 at 5:42 pm
Sure thing, Terry... I'd recommend solving the entire hierarchical problem and storing it in a separate table using Nested Sets. Here's a link...
http://www.codeproject.com/KB/database/nestedsets.aspx
... some folks end up converting the "adjacency model" that you currently have into the Nested Set model... I wouldn't do it. IMHO, it's easier to maintain the Adjacency Model and rerun the code to rebuild the Nested Set Model only when the hierarchy suffers a change.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2008 at 6:36 am
I believe the article that Jeff posted is pretty similar to the Joe Celko article - http://www.dbmsmag.com/9603d06.html
I'll be curious to see how this works out for you so, if you have time update later. Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 23, 2008 at 7:15 am
Thanks Jeff and David - I did a brief look-over and these appear to do what I need, with some tweaking of course. I'll post my final solution but, again, these appear to be a great start. I appreciate your help. :satisfied:
-- You can't be late until you show up.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply