Dealing with an employee tree (multiple branches)

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)

Dealing with an employee tree (multiple branches)

Author

Topic

Eniac
Starting Member

4 Posts

Posted - 2002-08-27 : 10:09:04

Hi, first, this post is a continuation of this one :
[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=18681[/url]

I'm posting as a new one because it's not the same question :)

-----------------------

I have a problem I've been trying to solve for quite some time now and I still haven't been able to solve it in order to get my results in 1 query.

First, please consider this table and the sample data (I hope tab'ing will be ok) fields 1, 2 and 3 are the keys:
1. ResourceID 2. ResourceIDParent 3. Path 4. Depth


-1- | -2- | -3-               | -4- 
-------------------------------------
11  | 0   | /                 | 1 
137 | 1   | /1/11/            | 3 
48  | 10  | /10/5/4/3/2/1/11/ | 8 
49  | 10  | /10/5/4/3/2/1/11/ | 8 
1   | 11  | /11/              | 2 
186 | 132 | /132/1/11/        | 4

This table allows an employee to have an infinite number of supervisors. Transforming the usual employee tree in what I call an Employee Web Structure.

It is actually implemented in our system and its working but I often have to do 2 queries to retrieve what I want. I'm sure it can be done in one query.

Now on to my problem. I'm trying to build a query that will :

1. Retrieve all supervisors above a specified user
2. Retrieve all employees below a specified user
3. Mark which of them are directly related to me.
4. Retrieve info about the specified user

Point 3 is the only left unsolved.
Point 1, 2 and 4 are solved using the following queries :
(Warning....big queries ahead)

/* Get All DIRECT Supervisors & Employees */
SELECT R.ResourceID, R.ResourceLevelID, R.resUserCode, R.resFirstName, R.resFamilyName, R.resPassword, R.ProvinceID, R.resPeriod, R.resEnabled, R.resDeleted, NULL as Depth, NULL as Path, 'Employee' AS Type
FROM Resource R
WHERE R.ResourceID=11

UNION

SELECT DISTINCT RP.ResourceIDParent, R.ResourceLevelID, R.resUserCode, R.resFirstName, R.resFamilyName, R.resPassword, R.ProvinceID, R.resPeriod, R.resEnabled, R.resDeleted, NULL as Depth, NULL as Path, 'Supervisor' AS Type
FROM ResourcePath RP INNER JOIN Resource R ON RP.ResourceIDParent = R.ResourceID
WHERE RP.ResourceIDParent <> 0 And RP.ResourceID=11

UNION

SELECT R.ResourceID, R.ResourceLevelID, R.resUserCode, R.resFirstName, R.resFamilyName, R.resPassword, R.ProvinceID, R.resPeriod, R.resEnabled, R.resDeleted, NULL as Depth, NULL as Path, 'Subordinate' AS Type
FROM ResourcePath RP INNER JOIN Resource R ON RP.ResourceID = R.ResourceID
WHERE ResourceIDParent=11
ORDER BY Type, Depth, R.resFamilyName, R.resFirstName

/* Get All Supervisors & Employees (Direct or not) */
SELECT R.ResourceID, R.ResourceLevelID, R.resUserCode, R.resFirstName, R.resFamilyName, R.resPassword, R.ProvinceID, R.resPeriod, R.resEnabled, R.resDeleted, NULL as Depth, NULL as Path, 'Employee' AS Type
FROM Resource R
WHERE R.ResourceID=11

UNION

SELECT DISTINCT R.ResourceID, R.ResourceLevelID, R.resUserCode, R.resFirstName, R.resFamilyName, R.resPassword, R.ProvinceID, R.resPeriod, R.resEnabled, R.resDeleted, RP.Depth, RP.Path, 'Supervisor' AS Type
FROM ResourcePath RP INNER JOIN Resource R ON RP.ResourceID = R.ResourceID CROSS JOIN
(SELECT ResourceIDParent, Path, Depth FROM ResourcePath WHERE ResourceID=11) ParentPaths
WHERE (ParentPaths.Path LIKE '%/' + CONVERT(VARCHAR(5), RP.ResourceID) + '/%')

UNION

SELECT R.ResourceID, R.ResourceLevelID, R.resUserCode, R.resFirstName, R.resFamilyName, R.resPassword, R.ProvinceID, R.resPeriod, R.resEnabled, R.resDeleted, RP.Depth, RP.Path, 'Subordinate' AS Type
FROM ResourcePath RP INNER JOIN Resource R ON RP.ResourceID = R.ResourceID
WHERE (RP.Path LIKE '%/11/%')
ORDER BY Type, RP.Depth DESC, R.resFamilyName, R.resFirstName

Is it in any way possible to use the query that retrieves all supervisors & employees (direct or not) and have it mark which of them are direct?

any help or insight would be greatly appreciated.

Thanks

Stéphane Dorion
-----------------------------------
There are 10 types of people in the
world: Those who understand binary,
and those who don't.
-----------------------------------

-- Eniac --

-----------------------------------
Experience is what makes you pause
briefly before going ahead making
the same mistake
-----------------------------------

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-28 : 15:50:23

What you can do, is to define an algorithm to go trough your tree. I don't know how deep is your tree, but lets say you go trough it from left to right. You attirbute 2 number for each person in your tree. When you begin 1 is your fist person( big boss ) then under him there is 2(vice president). The second number you give to your employee is when you can't go down anymore. Lets say there is only one employee under your first vice president, is two number will be 3 and 4. Then your first vice president will get 2 and 5. Your second vice-president will get 6 and 7( assuming there is no employee under him) Now you can simply query your tree like

select employee where firstNumber between(boss.firstNumber and boss.secondNumber)

I dont't know if it is clear, I'm not really good in english and it is not easy to abstract tree algorithm.

You can also use a case in your select to put a special number if the employee is directly under the boss.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources