multilevel hierarchy query

bruce@aristotle.net · *nix forums beginner Joined: 17 Feb 2005 Posts: 1

If you _know_ the number of levels of the hierarchy in advance, then
this is probably the way to go. Otherwise you're probably going to
need to resort to a recursive VBA code solution of some kind. It would
be an intriguing exercise to combine the two, i.e., write code to
determine the actual depth of the hierarchy based on your actual data
and then actually generate the SQL from code...

Bruce

baphensley · *nix forums beginner Joined: 15 Feb 2005 Posts: 3

David,

Thanks. That was an interesting read.

Unfortunately, I've inherited the tables and can't change them, just
read them.

However, I can estimate the maximum number of levels in the tree. With
this in mind, I tried a brute force approach. This seems to get all
the staff below a manager (as long as they're no more than 6 levels
deep). Not elegant, but it seems to be effective.

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,
Bruce

David Schofield · *nix forums beginner Joined: 16 Feb 2005 Posts: 1

Hi
If you are into hierarchies, take a look at "Trees in SQL" by Joe
Celko

http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=29530

David

baphensley · *nix forums beginner Joined: 15 Feb 2005 Posts: 3

Greg,

Thanks for the tip. However, I can't seem to find anything in the
Access 97 documentation on the SHAPE command. I think it may have been
introduced with Access 2000.

Bruce

baphensley · *nix forums beginner Joined: 15 Feb 2005 Posts: 3

Rauf,

Thanks! Excellent!!

This is perfect for my Oracle data.

Now I need to do the same in Access 97 tables. I can't find anything
similar
to CONNECT BY for Access 97.

Bruce

Rauf Sarwar wrote:

Greg Teets · *nix forums beginner Joined: 15 Feb 2005 Posts: 1

On 15 Feb 2005 10:17:15 -0800, "Rauf Sarwar" <rs_arwar@hotmail.com>
wrote:

Rauf Sarwar · *nix forums Guru Joined: 03 May 2005 Posts: 353

Bruce Hensley wrote:

Vincent M · *nix forums Guru Joined: 22 Feb 2005 Posts: 1332

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

Google