Home » SQL & PL/SQL » SQL & PL/SQL » Help in exists / not in (Oracle, 8.1.7,0.0 / Server Manager 3.1.7.0.0 / Windows Server 2003)
Help in exists / not in [message #389009] Fri, 27 February 2009 00:07 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hello,

I am having the following sql but I am getting all the lic_no from tmp_car_rec. I have attached the tables and datas used to get the result with the following.

Actually when I run I am getting the following result

ID LIC_NO
1 YM123
2 GH234
3 XD890
4 PA456
5 YH789
6 GB565
7 RD456
8 WA111
9 WP222
10 RU666

but wheres I need only the lic_no which doesn't satisfy the job_codes. The result should be

LIC_NO
YM123
XD890
PA456
RD456
WA111
WP222
RU666

Only these dont have the relevant job codes. When I used exists the result coming correctly

LIC_NO
GH234
YH789
GB565

select A.LIC_NO
from tmp_car_rec a, tmp_ro_hdr b, tmp_ro_jobs c
where A.ID=B.CARD_ID and B.ID=C.ROHD_ID
and C.JOB_CODE not in 
('T0501','T0502','T0503','T0504','T0505','T0506',
'T0102','T0103','T0104''T0105','T0106','T0107'
);


 
select A.LIC_NO
from tmp_car_rec a, tmp_ro_hdr b
where A.ID=B.CARD_ID and 
not exists (select null from tmp_ro_jobs c
where B.ID=C.ROHD_ID
and C.JOB_CODE in 
('T0501','T0502','T0503','T0504','T0505','T0506',
'T0102','T0103','T0104''T0105','T0106','T0107'
));


Can anyone help? I have attached the tables and records with insert statement.
  • Attachment: TESTING.sql
    (Size: 3.89KB, Downloaded 123 times)
Re: Help in exists / not in [message #389011 is a reply to message #389009] Fri, 27 February 2009 00:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Help in exists / not in [message #389014 is a reply to message #389009] Fri, 27 February 2009 00:17 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
SELECT a.lic_no 
FROM   tmp_car_rec a, 
       tmp_ro_hdr b, 
       tmp_ro_jobs c 
WHERE  a.id = b.card_id 
       AND b.id = c.rohd_id 
       AND c.job_code NOT IN ('T0501','T0502','T0503','T0504', 
                              'T0505','T0506','T0102','T0103', 
                              'T0104''T0105','T0106','T0107'); 


SELECT a.lic_no 
FROM   tmp_car_rec a, 
       tmp_ro_hdr b 
WHERE  a.id = b.card_id 
       AND NOT EXISTS (SELECT NULL 
                       FROM   tmp_ro_jobs c 
                       WHERE  b.id = c.rohd_id 
                              AND c.job_code IN ('T0501','T0502','T0503','T0504', 
                                                 'T0505','T0506','T0102','T0103', 
                                                 'T0104''T0105','T0106','T0107')); 


I have formatted. Can anyone help?
Re: Help in exists / not in [message #389055 is a reply to message #389014] Fri, 27 February 2009 02:21 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
kumarvk,

I am still not clear with your requirement. Posting the desired results with the corresponding job_code might help us to understand your requirement better. However just noticed a missing comma in the list of vaalues you passed for IN,

SELECT a.lic_no
  FROM tmp_car_rec a, tmp_ro_hdr b, tmp_ro_jobs c
 WHERE a.ID = b.card_id
   AND b.ID = c.rohd_id
   AND c.job_code NOT IN
          ('T0501',
           'T0502',
           'T0503',
           'T0504',
           'T0505',
           'T0506',
           'T0102',
           'T0103',
           'T0104''T0105',
    -- This will be considered as 'T0104'T0105'. Are you missing a comma here?
           'T0106',
           'T0107'
          ); 


Regards,
Jo
Re: Help in exists / not in [message #389059 is a reply to message #389055] Fri, 27 February 2009 02:36 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Sorry comma is missing.
Re: Help in exists / not in [message #389061 is a reply to message #389014] Fri, 27 February 2009 02:43 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
kumarvk wrote on Fri, 27 February 2009 07:17
I have formatted. Can anyone help?

OK, I will try to explain, that both queries return different results.

There are multiple rows in C table corresponding to one B.ID
The second query picks all rows in B not having any row with given JOB_CODE in C.
The first query picks all rows in C not having given JOB_CODE; then they are joined with B.

To your data: there are 3 rows in C table with C.ROHD_ID (corresponding to B.ID) with JOB_CODEs 'T0102', 'A0101' and 'B0103'.
The first query picks two rows (for last two JOB_CODEs).
The second query picks no row (because of the first JOB_CODE).

The equivalent NOT IN query would be
SELECT a.lic_no 
FROM   tmp_car_rec a, 
       tmp_ro_hdr b
WHERE  a.id = b.card_id 
  AND  b.id NOT IN ( SELECT c.rohd_id
                     FROM tmp_ro_jobs c 
                     WHERE c.job_code IN ('T0501','T0502','T0503','T0504', 
                                          'T0505','T0506','T0102','T0103', 
                                          'T0104''T0105','T0106','T0107'));

And have a look at joicejohn's answer; although it does not lead to the resultset difference (as it is contained in both queries), it may not be logically good.

[Edit: Added an example]

[Updated on: Fri, 27 February 2009 02:54]

Report message to a moderator

Previous Topic: xor check constraint
Next Topic: SOME.SYSTEM CLASS
Goto Forum:
  


Current Time: Fri Dec 09 13:33:02 CST 2016

Total time taken to generate the page: 0.12977 seconds