Re: A SQL statement question that cannot solve by myself

From: DanHW <danhw_at_aol.com>
Date: 16 Dec 1999 01:12:57 GMT
Message-ID: <19991215201257.22311.00001053_at_ng-fy1.aol.com>

>Hello all,
>
>I have a question related with SQL to query the result from tables, but
>still cannot resolve this problem up until now.
>
>Following tables:
>Table test1, Column A, M
>Table test2, Column A, N
>Table test3, Column A, B, X
>Table test4, Column A, B, Y
>Table test5, Column A, B, Z
>
>I want to do outer-join of these tables so I write:
>
>select test1.A, test1.M, test2.N, test3.X, test4.Y, test5.Z from
>test1, test2, test3, test4, test5 where
>test1.A = test2.A(+) and test1.A = test3.A(+) and
>test1.A = test4.A(+) and test1.A = test5.A(+)
>and test3.B = test4.B and test3.B = test5.B and test4.B = test5.B
>and test3.B in ('C1', 'C2') and test4.B in ('C1', 'C2') and
>test5.B in ('C1', 'C2')
>
>My question is, my SQL can only return the result if test3, test4 and
>test5 all have the same vaue of B. However, in the real situation, some
>B in test3 do not have a value in test4 and vica versa (also in the case
>of test3 against test5 and test4 against test5). So in this case, the
>above SQL cannot return these records. Could anyone give me hints to
>rewrite the above SQL such that it can also returns the above records?
>

When you get a "matched" row in an outer join, all the columns returned are null, so use a nvl to check/allow matches on the dummy rows...

• to match the dummy rows possibly generated from test3 in the join with test5, change the where condition to

...
where

test1.A = test2.A(+) and test1.A = test3.A(+) and
test1.A = test4.A(+) and test1.A = test5.A(+)
and nvl(test3.B,'x') =nvl(test4.B,'x') and nvl(test3.B,'x') =nvl(test5.B,'x')

 and nvl(test4.B,'x') =nvl(test5.B,'x')
and nvl(test3.B,'x') in ('x', 'C1', 'C2') and nvl(test4.B,'x') in ('x','C1', 'C2') and
nvl(test5.B,'x') in ('x','C1', 'C2')

(you can also embed a lot of "AND"s with ... IS NULLs, but this is a lot easier to follow)

Dan Hekimian-WIlliams Received on Thu Dec 16 1999 - 02:12:57 CET