By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,710 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,710 IT Pros & Developers. It's quick & easy.

multilevel hierarchy query

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

Bruce Hensley wrote:
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


You can find information about hierarchical queries at
http://download-west.oracle.com/docs...4a.htm#2053937

URL may wrap.

Regards
/Rauf

Nov 13 '05 #2

P: n/a
On 15 Feb 2005 10:17:15 -0800, "Rauf Sarwar" <rs******@hotmail.com>
wrote:

Bruce Hensley wrote:
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

I haven't used them yet but you may find the Shaped Query for ADO
helpful.

Just do a search in Google.

Good luck.
Greg Teets
Cincinnati Ohio USA
Nov 13 '05 #3

P: n/a
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:
Bruce Hensley wrote:
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
You can find information about hierarchical queries at

http://download-west.oracle.com/docs...4a.htm#2053937
URL may wrap.

Regards
/Rauf


Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

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

http://www.intelligententerprise.com...equestid=29530

David

Nov 13 '05 #6

P: n/a
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

Nov 13 '05 #7

P: n/a
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

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.