Newbie SQL question

Thank you to anyone who can find an answer to this .....

I am hitting an Oracle 7 db using odbc and visual basic in an active server page running off of an IIS web server

the problem I am having is that I have a table that has these fields user_id, name, supervisor

any user can be defined as a supervisor using their user_id (with no checking to make sure two people aren't each others supervisor creating a never ending loop)

what I am trying to do is select a user_id and from that determine everyone they are a supervisor for. With these names I want to list anyone they are supervisors for, etc, etc, until I get a distinct list of names that the top supervisor is supervisor for :)

The method I have tried looks like this

select distinct user_id, name from emp
where supervisor=user
  or supervisor in (select user_id from emp where supervisor=user) order by name

This gets me down two levels. I could keep adding "or supervisor in" statements but then I am only guessing on how many levels of management there are.

Any hints on how to do such a thing?

Thanks,
Steve

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 07 1999 - 00:00:00 CDT