472,342 Members | 1,389 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,342 software developers and data experts.

multilevel hierarchy query

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
7 10914

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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Mike N. | last post by:
Hello to all: First let me apologize for the length of this question, I've made an attempt to include as much information as is needed to help...
1
by: joey | last post by:
I have a SQL query where I get the count of something, but what I really want is a yes/no (boolean) answer. Here is my query: SELECT COUNT(*) AS...
34
by: Karam Chand | last post by:
Hello I have been working with Access and MySQL for pretty long time. Very simple and able to perform their jobs. I dont need to start a flame...
1
by: Sakcee | last post by:
Hi I have a script e.g. import package.module ID = "55" package.module.checkID()
7
salesmr
by: salesmr | last post by:
I have a query that works fine in Oracle, but I can't seem to get it to work in PHP. The code is: $query = "SELECT lpad(' ', (level - 1) * 4) ||...
4
by: BSB | last post by:
IIf(IsNull()=True And IsNull(!)=True,"UNKNOWN",IIf((!)="CHECK" And (Left(,2)="58" Or Left(,2)="59"),"Residential",IIf((!)="CHECK" And...
0
by: nintesa | last post by:
I'm using Oscommerce, Joomla, Mambo ecc... everyone of these have a good multilevel admin panel. Now I'm developing a new indipendent little...
2
by: Tem | last post by:
I have a table that looks like this. Each row is a reply or a topic when IsAReplyTo = 0 I need to write a query that pulls all rows of a hierarchy...
3
by: IAuditor | last post by:
Access 2003, XP - I have a multi-table query that is losing data. 1 table is a lookup (Hierarchy), and the other 3 are virtual duplicates (all with...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.