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
Problem with NOT EXISTS clause
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Problem with NOT EXISTS clause
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:
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:
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!
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)
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);
Hope this helps!
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Problem with NOT EXISTS clause
Thanks a lot!! will try this approach.