Re: Employee table SQL question

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/07/11
Message-ID: <33C70C6B.2C82@postoffice.worldnet.att.net>#1/1

Use the START WITH/CONNECT BY clause of the SELECT statement, along these lines:

SELECT ....
FROM Employee
START WITH Id= (SELECT Id FROM Employee WHERE Login =

                (SELECT USER FROM DUAL)
CONNECT BY Id = PRIOR Supervisor_ID;

For additional information about this type of queries, check out the book "ORACLE: The Complete Reference, 3rd Edition" by George Koch and Kevin Loney

Hope this helps.

Michael Serbanescu

---

Cleve Sharpe wrote:
>
> 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
> 2. That SUPERVISOR's SUPERVISOR Until we get to the
> head of the company whose SUPERVISOR is himself.
>
> Please help me.
> =-=-=-=-=-=-=-=-=-=-=-=-=
> Cleve
> e-mail: cleve_at_cbcsf.org
  Received on Fri Jul 11 1997 - 00:00:00 CDT