Re: Question: SQL outer join

It is possible to apply the outer join on multiple rows. The query is fine except for the following:

        and b.period = '01'
should instead read

        and b.period (+)= '01'

If a filter condition is applied to the table being outer joined to, the outer joins are ignored unless the outer join operator is also added to the filter condition.

Kurosh Hormozian
Visioneering Partners Inc.

Sheldon Wang <sheldon.wang_at_cdott.com> wrote in article <72sgml$chj$1_at_demon.uunet.ca>...
> Hi,
>
> I have a question about using the Outer join in SQL statement. I know I
can
> use the outer join on a single row in a table. However is it possible to
> apply the Outer join on multiple rows in one table? The following is my
> example:
>
> // outer joins with multiple rows (composite key). Is the following ok?
> // trans and detail are two tables.
>
> select
> a.cont_no,
> a.supertype_code,
> a.type_code,
> a.subtype_code,
> a.trans_no,
> a.region_code,
> b.value
>
> From
> trans a,
> detail b
>
> where
> a.cont_no= 'W8476-1-EN01/01NJ'
> and b.period = '01'
> and a.cont_no = b.cont_no(+)
> and a.supertype_code = b.supertype_code(+)
> and a.type_code = b.type_code(+)
> and a.subtype_code = b.subtype_code(+)
> and a.trans_no = b.irb_trans_no(+)
> and a.region_code = b.region_code(+)
>
> order by
> a.cont_no,
> a.region_code,
> a.trans_no,
> a.supertype_code,
> a.type_code,
> a.subtype_code
>
> // end of SQL
>
> I would appreciate your advice very much.
>
> Sheldon
>
>
>
Received on Tue Nov 17 1998 - 23:34:52 CST