Re: A SQL statement question that cannot solve by myself

If I understand your problem correctly, you may get the right results by using a UNION and also an outer-join on B:

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 test3.B in ('C1', 'C2')
UNION
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 test4.B = test3.B(+) and test4.B = test5.B(+) and test4.B in ('C1', 'C2')
UNION
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 test5.B = test3.B(+) and test5.B = test4.B(+) and test5.B in ('C1', 'C2')
/

<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?
>
> Thanks,
> David
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Dec 15 1999 - 14:13:40 CST