Re: Employee table SQL question

Cleve Sharpe <cleve_at_cbcsf.org> wrote in article <01bc8e1e$7c46a960$339ff5cf_at_cbcsf1.boca.net>...
> Hello everyone:
>
> I have an EMPLOYEE table with the following columns:
> ID,
> NAME,
> LOGIN *,
> SUPERVISOR_ID **
>
> * LOGIN is the same as SELECT USER FROM DUAL (Oracle Login);
> ** Supervisor_ID is the foreign key to the EMPLOYEE.ID column.
>
> I need the simplest SQL statement or PL/SQL script to get the following:
>
> 1. The SUPERVISOR of the EMPLOYEE who is logged in

a)
select * from employee
 where id=(select supervisor_id from employee where login=user);

> 2. That SUPERVISOR's SUPERVISOR Until we get to the
> head of the company whose SUPERVISOR is himself.
>

a)
select * from employee where id=
(select to_number(substr(max(to_char(level,'999')||id),4))   from employee
  start with login=user connect by id = prior supervisor_id) ;
b)
select * from
(select distinct -level ignore_field, * from employee   start with login=user connect by id = prior supervisor_id)  where rownum=1
;

-- 
-----------
G'luck!
Garry