Recursive results of a n-level hierarchy

I need to send a flat representation of our Acct Rep Team and Management groups to an external app. This data should be structed in a way that would allow access to the owned accts in a reporting environment to all Reps and their Managers. In other words I need any level of manager to have rights to all accounts they own and to accounts where a Rep anywhere below them is that account's Rep.

For example from these two given tables: Table: employee
REP_ID MGR_ID

1             null
2             1
3             1
4             3
5             3
6             4
7             5

Table: account
ACCT_ID ACCT_REP_ID

A             2
B             3
C             4
D             5
E             6
F             7

How would I get this result:
REP_OR_MGR_ID ACCT_ID

1                 A
1                 B
1                 C
1                 D
1                 E
1                 F
2                 A
3                 B
3                 C
3                 D
4                 C
4                 E
5                 D
5                 F
6                 E
7                 F

I thought connect by/start with was my solution but don't see how I can get the recursive results set desired. Any help is appreciated! Received on Tue Jan 04 2005 - 17:14:49 CST