Re: A SQL statement question that cannot solve by myself

<laulau823_at_my-deja.com> wrote in message news:8380ni$rok$1_at_nnrp1.deja.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?

Review your business rules and database design first. Why do you need such join conditions?

There are some semantic errors in your statment. For example, if "test1.A=test3.A(+)" is one condition, "test3.B in ('C1', 'C2')" is another,
you must rewrite to "test3.B(+) in ('C1', 'C2')". Received on Fri Dec 17 1999 - 09:35:40 CST