Monday, May 26, 2008

Outer Join Problem

Two tables need to be joined. They have the following columns.
Tbl1          Tbl2
---- ----
FieldA (key) FieldA (key)
FieldB FieldD
FieldC

Tbl1 data: Tbl2 data:
---------- ----------
1 1
5
6 6
7
We want to see everything on Tbl2 whether or not there is a record on Tbl1.

Even though we specify '(+)' to indicate an outer join, this will not work correctly since there is another condition involved on that table.
Select tbl2.FieldA, tbl2.FieldD, tbl1.FieldB 
From tbl1, tbl2
where tbl1.FieldA(+) = tbl2.FieldA ** DOESN'T WORK **
And tbl1.FieldC = '1';
Fix #1:
Select tbl2.FieldA, tbl2.FieldD, tbl1.FieldB 
From tbl1, tbl2
where tbl1.FieldA(+) = tbl2.FieldA
And tbl1.FieldC(+) = '1';
Fix #2:
Select tbl2.FieldA, tbl2.FieldD, tbl1.FieldB 
From (
Select *
From tbl1
Where FieldC(+) = '1' -- the condition is moved to a subquery
) tbl1,
tbl2
where tbl1.FieldA(+) = tbl2.FieldA;

No comments:

Post a Comment