Tbl1 Tbl2We want to see everything on Tbl2 whether or not there is a record on Tbl1.
---- ----
FieldA (key) FieldA (key)
FieldB FieldD
FieldC
Tbl1 data: Tbl2 data:
---------- ----------
1 1
5
6 6
7
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.FieldBFix #1:
From tbl1, tbl2
where tbl1.FieldA(+) = tbl2.FieldA ** DOESN'T WORK **
And tbl1.FieldC = '1';
Select tbl2.FieldA, tbl2.FieldD, tbl1.FieldBFix #2:
From tbl1, tbl2
where tbl1.FieldA(+) = tbl2.FieldA
And tbl1.FieldC(+) = '1';
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