RE: Query Help

[Naveen Nahata]

SQL> SELECT * FROM emp;   NAME              EMP       BOSS ---------- ---------- ---------- SAMAR              10         20 ASHOK              20         30 ASHWINI            30         40 MONIKA             11         21 RASHI              21         31 SMRITI             12         22 SUMEET             22         32   7 rows selected.   SQL> SELECT   *   2  FROM      emp   3  WHERE   emp NOT IN ( SELECT     emp   4                                      FROM        emp   5                                      START WITH emp = 10 CONNECT BY PRIOR emp = boss)   6  AND        emp NOT IN ( SELECT     emp   7                                      FROM        emp   8                                      START WITH emp = 10 CONNECT BY PRIOR boss = emp);   NAME              EMP       BOSS ---------- ---------- ---------- MONIKA             11         21 RASHI              21         31 SMRITI             12         22 SUMEET             22         32   SQL>   -----Original Message----- From: Samar Saxena [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 26, 2002 4:09 PM To: Multiple recipients of list ORACLE-L Subject: Query Help   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. --------------------------------------------------------------------------------------------------------