Re: SQL statement question...

From: Jurij Modic <jmodic_at_src.si>
Date: Thu, 28 Oct 1999 20:27:33 GMT
Message-ID: <381aad67.5120940@news.siol.net>

On Thu, 28 Oct 1999 15:01:31 +0200, "Sybrand Bakker" <postmaster_at_sybrandb.demon.nl> wrote:

>Homework, definitely. But anyway...
>select aaa.*, bbb.*
>from aaa, bbb
>where bbb.a1(+) = aaa.a1;
>
>and that's all.

Unfortunately this is not quite correct. To achieve what Jimmy wants one should outer-join the tables from both sides. As (+) operator in Oracle can be used on only one of the two tables being joined, we must use two selects with an UNION:

select aaa.*, bbb.*
from aaa, bbb
where bbb.a1(+) = aaa.a1
UNION
select aaa.*, bbb.*
from aaa, bbb
where bbb.a1 = aaa.a1(+);

This will return all three rows, as Jimmy wanted.

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Oct 28 1999 - 15:27:33 CDT