|
|
|
|
|
|
|
|
|
|
|
|
|
Re: duplicate records [message #293026 is a reply to message #293017] |
Thu, 10 January 2008 11:29   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Try replacing the "OR" with "And" in your not in clause, see if that helps.
Alternatively, try to use union something like
select * from table_a
where <column> not in (select val from table_b
union
select val from table_c
...)
HTH
Regards
Raj
[Updated on: Thu, 10 January 2008 11:32] Report message to a moderator
|
|
|
|
Re: duplicate records [message #293138 is a reply to message #292661] |
Fri, 11 January 2008 00:22   |
 |
rajavu1
Messages: 1574 Registered: May 2005 Location: Bangalore , India
|
Senior Member |

|
|
I think what Ashish trying to convey is something diffrerent . If I am not wring , he is trying to achieve something like ,
SQL> SELECT ID, DUP_COL1, DUP_COL2 FROM TABLE1;
ID DUP_C D
---------- ----- -
1 11 Y
2 11 Y
3 22 Y
4 22 Y
5 33 Y
6 33 Y
7 44 Y
8 44 Y
9 55 Y
10 55 Y
11 66 Y
12 66 Y
13 77 Y
14 77 Y
15 88 Y
16 88 Y
17 99 Y
18 99
19 00 Y
20 00 Y
20 rows selected.
SQL> SELECT * from (
2 select ID , DUP_COL1,DUP_COL2 ,
3 (select count(*) from table1 t1
4 WHERE t1.DUP_COL1 = T.DUP_COL1
5 AND NVL(T1.DUP_COL2,'X') = NV
L(T.DUP_COL2,'X') ) CNT
6 from table1 T
7 where ID not in (select TABLE1_ID from table2
8 union
9 select TABLE1_ID from table3))
10 WHERE CNT >1;
ID DUP_C D CNT
---------- ----- - ----------
2 11 Y 2
4 22 Y 2
6 33 Y 2
8 44 Y 2
10 55 Y 2
12 66 Y 2
6 rows selected.
SQL>

Rajuvan.
[Updated on: Fri, 11 January 2008 01:27] Report message to a moderator
|
|
|
|
|
|
|
|