Home » SQL & PL/SQL » SQL & PL/SQL » Query Help
Query Help [message #204299] Mon, 20 November 2006 01:07 Go to next message
prabhuapps
Messages: 79
Registered: June 2005
Location: Bangalore
Member
Hi All,

From the following resultset i need to find out the item_no which does not have organization_id 100.

SQL> select * from item_test;

    SL_NO   ITEM_NO ORGANIZATION_ID
--------- --------- ---------------
        1        10             100
        2        10             101
        3        10             102
        4        11             100
        5        11             102
        6        11             103
        7        12             103
        8        12             102

8 rows selected.


The output should be Item_No 12. Because it does not have organization_id 100.

Thanks in Advance.

Regards,
Prabhu

Re: Query Help [message #204301 is a reply to message #204299] Mon, 20 November 2006 01:24 Go to previous messageGo to next message
prabhuapps
Messages: 79
Registered: June 2005
Location: Bangalore
Member
I have tried and found one method of getting item_no 12 as result...

SQL> select distinct item_no 
  2  from item_test
  3  where organization_id != 100
  4  minus
  5  select distinct item_no
  6  from item_test
  7  where organization_id = 100;

  ITEM_NO
---------
       12


Is there any other method to get item_no 12 in single query ?

Please Suggest Me

Regards,
Prabhu
Re: Query Help [message #204328 is a reply to message #204301] Mon, 20 November 2006 03:20 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Another option is using a "where not exists".

In your query, the minus will filter out duplicate rows, so the distinct is not necessary.
Previous Topic: Rowid
Next Topic: Audit on password change
Goto Forum:
  


Current Time: Sat Dec 10 08:47:43 CST 2016

Total time taken to generate the page: 0.08520 seconds