multilevel hierarchy

B

Bruce Hensley

I don't know how to begin on a query (SELECT statement) to find all the
tasks assigned to an arbitrary manager (say, staffID='JSmith') and her
organization, that is, assigned to all her underlings, and their underlings,
and .... For that matter, I don't even know how to find everyone in her
organization (at all levels).

- All individuals have only one manager
- Tasks are assigned to individuals
- A manager at any level may have direct reports and sub-managers

The table structure:

tblStaff
-------------
staffID
reportsToID (staffID of direct manager)


tblTasks
--------------
taskID
assignedToID (staffID of individual responsible for task)


Any help would be greatly appreciated.

Bruce
 
M

Michel Walsh

Hi,

If there is a predetermined fix number of level, you can try using
joins. Otherwise, you may wish to explore nested sets.


With a table ParChi (Parent-Child)

ParChi
who parent qtyper
1 1 1
2 1 4
3 1 5
4 2 3
5 4 1
6 2 3



with the query

SELECT ParChi_3.who, ParChi_3.parent, ParChi_2.parent, ParChi_1.parent,
ParChi.parent
FROM ((ParChi LEFT JOIN ParChi AS ParChi_1 ON ParChi.who = ParChi_1.parent)
LEFT JOIN ParChi AS ParChi_2 ON ParChi_1.who = ParChi_2.parent) LEFT JOIN
ParChi AS ParChi_3 ON ParChi_2.who = ParChi_3.parent
WHERE (((ParChi_3.who) Is Not Null));


you get

Query49
who ParChi_3.parent ParChi_2.parent ParChi_1.parent ParChi.parent
5 4 2 1 1
3 1 1 1 1
2 1 1 1 1
1 1 1 1 1
6 2 1 1 1
4 2 1 1 1




where the ParChi_i.Parent list all the parents of a given "who" (with
repetition of the big boss, parent=1, to fill the list, such as it may be
useful to get a bill of material).

Note the query implies the table four times, since there are at most 4
layers involved. If a fifth layer was to be added, the query will just
fail... unless we manually add, by hand, the extra code.



Hoping it may help,
Vanderghast, Access MVP
 
M

[MVP] S.Clark

Back in the days when I was an Access Instructor, I used to put this under
the category of "Stupid Database Tricks". But, every once in a while
someone needs it. To give it a real name, it is a Self, Right, Outer Join.

Here is an example from Northwind that shows an Employee, that Employees
supervisor(if it exists), and that employee's supervisor's supervisor(if it
exists.)
SELECT Employee.LastName AS EmpLN, Employee.FirstName AS EmpFN,
Supervisor.LastName AS SupLN, Supervisor.FirstName AS SupFN,
SuperSupervisor.LastName AS Sup2LN, SuperSupervisor.FirstName AS Sup2FN
FROM Employees AS SuperSupervisor RIGHT JOIN (Employees AS Supervisor RIGHT
JOIN Employees AS Employee ON Supervisor.EmployeeID = Employee.ReportsTo) ON
SuperSupervisor.EmployeeID = Supervisor.ReportsTo;
 
B

baphensley

MW and SC,

Thanks for the help.

I finally came to a similar conclusion.

Unfortunately, the number of levels is not consistent. But, I can
estimate a maximum number of levels; so I was able to use a string of
self joins that went deeper than the lowest level. This succeeded
(though I'm sure can be done much cleaner).

Here is a parameter query to find all staff under a boss, not matter
how far down the tree (as long as it's no more than 6 levels) ...

SELECT tblStaff.StaffID, [tblStaff]![StaffID] & " " &
[tblStaff]![ReportsToID] & " " & [B2]![ReportsToID] & " " &
[B3]![ReportsToID] & " " & [B4]![ReportsToID] & " " &
[B5]![ReportsToID] AS ChainOCmd
FROM ((((tblStaff LEFT JOIN tblStaff AS B1 ON tblStaff.ReportsToID =
B1.StaffID) LEFT JOIN tblStaff AS B2 ON B1.ReportsToID = B2.StaffID)
LEFT JOIN tblStaff AS B3 ON B2.ReportsToID = B3.StaffID) LEFT JOIN
tblStaff AS B4 ON B3.ReportsToID = B4.StaffID) LEFT JOIN tblStaff AS B5
ON B4.ReportsToID = B5.StaffID
WHERE ((([tblStaff]![StaffID] & " " & [tblStaff]![ReportsToID] & " " &
[B2]![ReportsToID] & " " & [B3]![ReportsToID] & " " &
[B4]![ReportsToID] & " " & [B5]![ReportsToID]) Like "*" & [Boss: ] &
"*"));


Thanks again,
Bruce
 
M

Michel Walsh

Hi,


If the tree is relatively stable, you can use a nested set
representation. Basically, a nested set representation has nodes with two
values, lft and rgt (left and right, but since those are reserved
words...). You start worm (counter) =1, and make the "actual node under
consideration" the big boss,

1- for the actual node, lft=worm; worm = worm+1;
2- If the actual node has children with their rgt empty, take one of them as
the actual node, goto 1
3- if the actual node has no children left with their rgt empty, mark the
actual node rgt=worm; then make worm=worm+1; then, make its parent as the
actual node, goto 2
4- sure, if steps 3 find the actual node without parent, you are done the
labeling of lft and rgt values.


Example: A has B, C and D as children. B has E and F while C and G and F
has H:

A.lft=1
B.lft=2
E.lft=3
E.rgt=4
F.lft =5
H.lft=6
H.rgt=7
F.rgt=8
B.rgt=9
C.lft=10
G.lft=11
G.rgt=12
C.rgt=13
D.lft=14
D.rgt=15
A.rgt=16


Once we have the lft and rgt values, among other interesting properties:

we can get all the nodes under X by:

SELECT *
FROM Nodes
WHERE Nodes.lft BETWEEN X.lft AND X.rgt


We can get all the boss of X with

SELECT *
FROM Nodes
WHERE X.lft BETWEEN Nodes.lft AND Nodes.rgt


The "level" of node X

SELECT COUNT(*)
FROM Nodes
WHERE X.lft BETWEEN Nodes.lft AND Nodes.rgt


How many nodes there is under X

(1+X.rgt-X.lft)/2


If a node is a terminal (leaf):

rgt=lft+1




Those quite simple statements work whatever the number of levels there can
be in the tree... thanks to the "precompiled" values lft and rgt.



Hoping it may help,
Vanderghast, Access MVP


baphensley said:
MW and SC,

Thanks for the help.

I finally came to a similar conclusion.

Unfortunately, the number of levels is not consistent. But, I can
estimate a maximum number of levels; so I was able to use a string of
self joins that went deeper than the lowest level. This succeeded
(though I'm sure can be done much cleaner).

Here is a parameter query to find all staff under a boss, not matter
how far down the tree (as long as it's no more than 6 levels) ...

SELECT tblStaff.StaffID, [tblStaff]![StaffID] & " " &
[tblStaff]![ReportsToID] & " " & [B2]![ReportsToID] & " " &
[B3]![ReportsToID] & " " & [B4]![ReportsToID] & " " &
[B5]![ReportsToID] AS ChainOCmd
FROM ((((tblStaff LEFT JOIN tblStaff AS B1 ON tblStaff.ReportsToID =
B1.StaffID) LEFT JOIN tblStaff AS B2 ON B1.ReportsToID = B2.StaffID)
LEFT JOIN tblStaff AS B3 ON B2.ReportsToID = B3.StaffID) LEFT JOIN
tblStaff AS B4 ON B3.ReportsToID = B4.StaffID) LEFT JOIN tblStaff AS B5
ON B4.ReportsToID = B5.StaffID
WHERE ((([tblStaff]![StaffID] & " " & [tblStaff]![ReportsToID] & " " &
[B2]![ReportsToID] & " " & [B3]![ReportsToID] & " " &
[B4]![ReportsToID] & " " & [B5]![ReportsToID]) Like "*" & [Boss: ] &
"*"));


Thanks again,
Bruce
Click to expand...
 
Top