A SQL statement question that cannot solve by myself

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?

Thanks,
David

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 15 1999 - 06:14:44 CST