Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: multilevel hierarchy query

Re: multilevel hierarchy query

From: baphensley <baphensley_at_usa.net>
Date: 16 Feb 2005 10:17:22 -0800
Message-ID: <1108577842.173931.131800@f14g2000cwb.googlegroups.com>


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 Received on Wed Feb 16 2005 - 12:17:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US