multilevel hierarchy query

[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

[Rauf Sarwar]

Re: multilevel hierarchy query


Bruce Hensley wrote:[color=blue]
> I don't know how to begin on a query (SELECT statement) to find all[/color]
the[color=blue]
> tasks assigned to an arbitrary manager (say, staffID='JSmith ') and[/color]
her[color=blue]
> organization, that is, assigned to all her underlings, and their[/color]
underlings,[color=blue]
> and .... For that matter, I don't even know how to find everyone in[/color]
her[color=blue]
> 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[/color]

You can find information about hierarchical queries at

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries4a.htm#2053937

URL may wrap.

Regards
/Rauf

[Greg Teets]

Re: multilevel hierarchy query

On 15 Feb 2005 10:17:15 -0800, "Rauf Sarwar" <rs_arwar@hotma il.com>
wrote:
[color=blue]
>
>Bruce Hensley wrote:[color=green]
>> I don't know how to begin on a query (SELECT statement) to find all[/color]
>the[color=green]
>> tasks assigned to an arbitrary manager (say, staffID='JSmith ') and[/color]
>her[color=green]
>> organization, that is, assigned to all her underlings, and their[/color]
>underlings,[color=green]
>> and .... For that matter, I don't even know how to find everyone in[/color]
>her[color=green]
>> 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[/color]
>[/color]
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

[baphensley]

Re: multilevel hierarchy query

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:[color=blue]
> Bruce Hensley wrote:[color=green]
> > I don't know how to begin on a query (SELECT statement) to find all[/color]
> the[color=green]
> > tasks assigned to an arbitrary manager (say, staffID='JSmith ') and[/color]
> her[color=green]
> > organization, that is, assigned to all her underlings, and their[/color]
> underlings,[color=green]
> > and .... For that matter, I don't even know how to find everyone[/color][/color]
in[color=blue]
> her[color=green]
> > 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[/color]
>
> You can find information about hierarchical queries at
>[/color]
http://download-west.oracle.com/docs...4a.htm#2053937[color=blue]
>
> URL may wrap.
>
> Regards
> /Rauf[/color]

[baphensley]

Re: multilevel hierarchy query

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

[David Schofield]

Re: multilevel hierarchy query

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]

Re: multilevel hierarchy query

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.StaffI D, [tblStaff]![StaffID] & " " &
[tblStaff]![ReportsToID] & " " & [B2]![ReportsToID] & " " &
[B3]![ReportsToID] & " " & [B4]![ReportsToID] & " " &
[B5]![ReportsToID] AS ChainOCmd
FROM ((((tblStaff LEFT JOIN tblStaff AS B1 ON tblStaff.Report sToID =
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

[bruce@aristotle.net]

Re: multilevel hierarchy query

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