Re: ANSI Outer Joins

Thanks, that appears to work!

Now all I need is a solution for Oracle 7... ;-)

Simon

Paul Moore wrote in message <364c200b.11218894_at_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 Mon Nov 16 1998 - 05:49:25 CST