Re: Newbie SQL question

In article <7tik47$vjq$1_at_nnrp1.deja.com>,   steve_billingsley_at_my-deja.com wrote:
> 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

Use the CONNECT BY clause. A variation of this should work for you:

select user_id, name
 from emp
start with name='Joe'
connect by supervisor = prior user_id;

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3700 <<<NOTE new number


Sent via Deja.com http://www.deja.com/
Before you buy.