I am using an ORACLE databse and having a real problem figuring out how to do a "roll-up" for manager reporting. I do not have access to write to another database, and I only have read access to the current one. So I have a table with the following fields: EMAIL_ADR, PERSON_ID, SUPERVISOR_ID Person_ID is uniqie for each user, and when they are the supervisor for a user, their PERSON_ID shows up in that persons SUPERVISOR_ID field. For example, lets say I have the following table: EMAIL_ADR, PERSON_ID, SUPERVISOR_ID bob@email.com, 2, 1 jim@email.com, 3, 1 sue@email.com, 4, 2 jay@email.com, 5, 3 mike@email.com, 6, 4 sean@email.com, 7, 5 lee@email.com, 8, 6 fred@email.com, 9, 7 What I am attempting to do is get the EMAIL_ADR for every user who ultimately rolls up to PERSON_ID 1, which would be everyone on the list. I tried the following: select EMAIL_ADR from TABLE where PERSON_ID in ( select PERSON_ID from TABLE where SUPERVISOR_ID in ( select PERSON_ID from TABLE where SUPERVISOR_ID in ( select PERSON_ID from TABLE where SUPERVISOR_ID in ( select PERSON_ID from TABLE where SUPERVISOR_ID in ( select PERSON_ID from TABLE where SUPERVISOR_ID in ('1'); Any help would be greatly appreciated. I am at a complete loss here.

Try the following: SELECT EMAIL_ADR FROM TABLE CONNECT BY SUPERVISOR_ID = PERSON_ID

That is the right track I think, but unfortunately in my live data we have some circular references. In reality we have Bob reporting to Sue, but Sue is also listed as reporting to Bob. In doing some google searching, it seems version 10g has a way of overcoming that, which is what I am looking into now. Though if anyone has any suggestesions, I'd be happy to hear them.

For those interested, I found the solution to this problem: select email_adr from SP_IDENTITY_MASTER start with person_id = '1' connect by nocycle prior person_ID = supervisor_id;

Isn't that what nev1 suggested? Life's more painless for the brainless.

Jennifer, Umm, no. It was close, and pointed me in the right directeion. I ran into a problem with looping, which is why I needed the "connect by nocycle prior" and not just "connect by".