Problem with NOT EXISTS clause

[nikx_8_7]

I have a requirement where i have to join 2 tables as per following conditions:-
1)inner Join on 3 keys (acctno1,acctno2,acctno3) from table1 with table2
2)for the unmatched records from step1 join the 2 tables on only 2 keys (acctno1,acctno2)
3)for the unmatched records from step2 join the 2 tables on only 1 key (acctno1)

i have tried doing this in following query:-

select
a.acctno1,
a.acctno2,
a.acctno3,
b.col1
from
table1 a inner join table2 b on
a.acctno1=b.acctno1 and a.acctno2=b.acctno2 and a.acctno3=b.acctno3
UNION
select
a.acctno1,
a.acctno2,
a.acctno3,
b.col1
from
table1 a inner join table2 b on
a.acctno1=b.acctno1 and a.acctno2=b.acctno2
WHERE NOT EXISTS(
select
a.acctno1,
a.acctno2,
a.acctno3,
b.col1
from
table1 a inner join table2 b on
a.acctno1=b.acctno1 and a.acctno2=b.acctno2 and a.acctno3=b.acctno3)
UNION
select
a.acctno1,
a.acctno2,
a.acctno3,
b.col1
from
table1 a inner join table2 b on
a.acctno1=b.acctno1
WHERE NOT EXISTS(
select
a.acctno1,
a.acctno2,
a.acctno3,
b.col1
from
table1 a inner join table2 b on
a.acctno1=b.acctno1 and a.acctno2=b.acctno2
WHERE NOT EXISTS(
select
a.acctno1,
a.acctno2,
a.acctno3,
b.col1
from
table1 a inner join table2 b on
a.acctno1=b.acctno1 and a.acctno2=b.acctno2 and a.acctno3=b.acctno3)
)

BUt it is not resulting any record from 2nd & 3rd part where i am using where exists
Any idea on how can we achieve this?
table1
acctno1,acctno2,acctno3
1,2,3
1,2,4
2,1,5

table2
acctno1,acctno2,acctno3,col1
1,2,3,a
1,2,5,b
2,2,4,c

o/p
acctno1,acctno2,acctno3,col1
1,2,3,a
1,2,4,b
2,1,5,c

[JimKnicely]

Hi,

I think you need to "correlate" your sub-queries in the NOT EXISTS clauses to their top query...

For example, the second query should look like this:

Code: Select all

select a.acctno1,
       a.acctno2,
       a.acctno3,
       b.col1
  from table1 a
  join table2 b
    on a.acctno1=b.acctno1
   and a.acctno2=b.acctno2
 where not exists (select null
                     from table1 a2
                     join table2 b2
                       on a2.acctno1=b2.acctno1
                      and a2.acctno2=b2.acctno2
                      and a2.acctno3=b2.acctno3
                    where a2.acctno1=a.acctno1
                      and a2.acctno2=a.acctno2
                      and a2.acctno3=a.acctno3)

The big difference in the above query from yours is the addition of the WHERE clause in the sub-query...

So your entire query might look like this:

Code: Select all

select a2.acctno1,
       a2.acctno2,
       a2.acctno3,
       b2.col1
  from table1 a2
  join table2 b2
    on a2.acctno1=b2.acctno1
   and a2.acctno2=b2.acctno2
   and a2.acctno3=b2.acctno3
 union
select a.acctno1,
       a.acctno2,
       a.acctno3,
       b.col1
  from table1 a
  join table2 b
    on a.acctno1=b.acctno1
   and a.acctno2=b.acctno2
 WHERE NOT EXISTS (select null
                     from table1 a2
                     join table2 b2
                       on a2.acctno1=b2.acctno1
                      and a2.acctno2=b2.acctno2
                      and a2.acctno3=b2.acctno3
                    where a2.acctno1=a.acctno1
                      and a2.acctno2=a.acctno2
                      and a2.acctno3=a.acctno3)
 union
select a.acctno1,
       a.acctno2,
       a.acctno3,
       b.col1
  from table1 a
  join table2 b
    on a.acctno1=b.acctno1
 WHERE NOT EXISTS (select null
                     from table1 a2
                     join table2 b2
                       on a2.acctno1=b2.acctno1
                      and a2.acctno2=b2.acctno2
                    WHERE NOT EXISTS (select null
                                        from table1 a3
                                        join table2 b3
                                          on a3.acctno1=b3.acctno1
                                         and a3.acctno2=b3.acctno2
                                         and a3.acctno3=b3.acctno3
                                       where a3.acctno1=a2.acctno1
                                         and a3.acctno2=a2.acctno2
                                         and a3.acctno3=a2.acctno3)
                      and a2.acctno1=a.acctno1
                      and a2.acctno2=a.acctno2);

Please test this to make sure it works! The point I'm trying to get across is the need to correlate the sub-queries

Hope this helps!

[nikx_8_7]

Thanks a lot!! will try this approach.