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?

[Quoted] 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 - 16:35:40 CET