Hi,
Using Oracle 11g R2.
I'd like to outer join 2 tables together and place a restrictions on the types of records which should be returned in the query result. Here is a mock up of the tables and data.
create table aaa (col1 number not null, col2 number not null)
create table bbb (col1 number not null, col2 number not null)
insert into aaa values (1, 80)
insert into aaa values (2, 90)
insert into aaa values (3, 80)
insert into aaa values (4, 90)
insert into aaa values (5, 80)
insert into bbb values (3, 600)
insert into bbb values (4, 700)
Here's the query
select a.col1, a.col2, b.col1, b.col2
from aaa a, bbb b
where a.col1 = b.col1 (+)
and (a.col2, b.col2) <> ((90, 700))
The query result shows as follows.
col1 col2 col1 col2
1 80
3 80 3 600
5 80
Where col1 = 4 has been removed which is an expected result. However, where col1 = 2 has also been removed, which is not a desired result. Your response is appreciated.