Re: A SQL statement question that cannot solve by myself

From: James Lorenzen <james_lorenzen_at_allianzlife.com>
Date: Wed, 15 Dec 1999 21:12:16 GMT
Message-ID: <83907b$k69$1_at_nnrp1.deja.com>

What you "need" to have is multiple table outer joins. Oracle will not allow that. To get around this we need to remember that NULLS are returned as column values from an outer join with no match. These sneaky NULLS must be accounted for in your WHERE clause. So, if I understand your question, here goes my take on it:

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(+)

-- Above are the outer joins. It gets tricky for the "B" column match.

 AND ((test3.A IS NULL OR test4.A IS NULL) OR (test3.B = test4.B))
 AND ((test3.A IS NULL OR test5.A IS NULL) OR (test3.B = test5.B))
 AND ((test4.A IS NULL OR test5.A IS NULL) OR (test4.B = test5.B))
 AND (test3.B IS NULL OR test3.B IN ('C1', 'C2'))
 AND (test4.B IS NULL OR test4.B IN ('C1', 'C2'))
 AND (test5.B IS NULL OR test5.B IN ('C1', 'C2'))

HTH
    James

In article <8380ni$rok$1_at_nnrp1.deja.com>,   laulau823_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 15 1999 - 22:12:16 CET