Re: ANSI Outer Joins

From: Paul Moore <paul.moore_at_uk.origin-it.com>
Date: Fri, 13 Nov 1998 13:13:15 +0100
Message-ID: <364c200b.11218894@news.origin-it.com>

On Thu, 12 Nov 1998 17:58:51 +0100, "Simon Caldwell" <simonATgetrealsystemsDOTcom> wrote:

>
>Paul Moore wrote in message <364af9de.21722496_at_news.origin-it.com>...
>>I don't understand your question. The syntax you quote works on
>>Oracle. If the syntax you are using is different, please post it...
>>
>No it doesn't. Note that A is outer joined to both B and C. This will give
>an error
>ORA-01416: two tables cannot be outer-joined to each other

Ah. Sorry, I missed that point. An'd I'd forgotten that restriction, too.

>The SQL-92 syntax would be
>select a.col1, b.col2, c.col3
>from b
> JOIN c ON (b.col4=c.col4)
> LEFT OUTER JOIN a ON (a.col1 = b.col1 AND a.col2 = c.col1)
>

Oh, yes - I know that syntax (or a variant) from Microsoft SQL. I always hated it, as I could never work out what was going on :-)

How about (untested) using a select statement in the FROM part - sort of like

select

a.col1, x.bc2, x.cc3
from

a,
(select b.col1 bc1, b.col2 bc2, c.col1 cc1, c.col3 cc3

     from b, c
     where b.col4 = c.col4) x

where

a.col1(+) = x.bc1 and
a.col2(+) = x.cc1

I think this sort of thing works, although I haven't tried it myself... It's new in Oracle 8, so not available if you're running Oracle 7.

Paul Moore. Received on Fri Nov 13 1998 - 06:13:15 CST