RE: Query Help

From: Naveen Nahata <naveen_nahata_at_mindtree.com>
Date: Thu, 26 Dec 2002 04:38:39 -0800
Message-ID: <F001.005223EE.20021226043839@fatcity.com>

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-----
Sent: Thursday, December 26, 2002 4:09 PM To: Multiple recipients of list ORACLE-L  

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: Naveen Nahata
  INET: naveen_nahata_at_mindtree.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 - 06:38:39 CST