Oracle-L: Query Help

From: Samar Saxena <samar.saxena_at_igtsolutions.com>
Date: Thu, 26 Dec 2002 02:39:00 -0800
Message-ID: <F001.005223A6.20021226023900@fatcity.com>

Hi people,

I have this table X, whose structure is described below:

Name                                Null?    Type
-------------------------------------- -------- ------------------------
NAME                                          VARCHAR2(20)
EMP                                             NUMBER(5)
BOSS                                           NUMBER(5)

The table is having the following data:

NAME                                   EMP    BOSS
------------------------------------------ ----------- ----------
SAMAR                                  10         20
ASHOK                                  20         30
ASHWINI                                30         40
MONIKA                                 11         21
RASHI                                    21         31
SMRITI                                   12         22
SUMEET                                22         32

The table is storing the employee and its boss relationship in a hierarchical format. eg. employee 10's boss is 20. employee 20's boss is 30 and 30's boss is 40.

Now my question is that I want to select all the records from the table where the employee no. returned doesn't have any hierarchical relationship with the employee no. passed in the where condition of the query.
for eg. if

  select ....
  from X
  where ......
  and emp = 10;

then I don't want the following records :

SAMAR                     10         20
ASHOK                     20         30
ASHWINI                   30         40

But want all the following records:

MONIKA                    11         21
RASHI                       21         31
SMRITI                      12         22
SUMEET                   22         32

Awaiting help.....

thanks,

Samar

CONFIDENTIALITY/PROPRIETARY NOTE This communication is confidential / proprietary and is intended for use only by the addressee. IGT Solutions Private Limited accepts no responsibility for any mistransmission of,or interference with, this communication.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Samar Saxena
INET: samar.saxena_at_igtsolutions.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Dec 26 2002 - 04:39:00 CST