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

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

URL may wrap.

Regards
/Rauf

On 15 Feb 2005 10:17:15 -0800, "Rauf Sarwar" <rs_arwar (AT) hotmail (DOT) com>
wrote:

helpful.

Just do a search in Google.

Good luck.
Greg Teets
Cincinnati Ohio USA

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,

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

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

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

David

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

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