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  |
kumarvk
Messages: 214 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 696 times)
|
|
|
|
Re: Help in exists / not in [message #389014 is a reply to message #389009] |
Fri, 27 February 2009 00:17   |
kumarvk
Messages: 214 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   |
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 #389061 is a reply to message #389014] |
Fri, 27 February 2009 02:43  |
flyboy
Messages: 1903 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
|
|
|
Goto Forum:
Current Time: Tue Feb 11 18:19:27 CST 2025
|