Re: Twin outer joins

Strictly speaking, won't

    Select *
    From Table1, Table2, Table2 Table2b, Table3     Where

        Table1.A1 = Table2.A2 (+) and
        Table3.B3 = Table2b.B2 (+)

..work as well?

Can somebody comment as to the performance implications of this, please? I'm curious..

Thanks.

--steve

"corey lawson" <corey.lawson_at_worldnet.att.com> wrote in message news:3c8c1e15.30019263_at_netnews.att.net...
> select table2.*
> from table2,
> (select table1.field1, table3.field2
> from table1, table3 /* yes, a cartesian join */
> ) joinme
> where table2.field1 = joinme.field1 (+) and table2.field2 =
> joinme.field2 (+)
>
> Better (probably much better), stick the cleaned-up version of the
> cartesian join into a table where you can put indexes on field1 and
> field2.
>
> On Wed, 30 Jan 2002 13:03:39 +0000, "Daniel A. Morgan"
> <damorgan_at_exesolutions.com> wrote:
>
> >One way is with an in-line view such as
> >
> >SELECT * FROM (
> >SELECT *
> >FROM table1, table2
> >WHERE table1.A1 = table2.A2(+)
> >UNION ALL
> >SELECT *
> >FROM table3, table2
> >WHERE table3.B3 = table2.B2(+)
> >);
> >
> >Daniel Morgan
> >
> >
> >
> >
> >JGP wrote:
> >
> >> I have a query like this:
> >>
> >> Select *
> >> From Table1, Table2, Table3
> >> Where
> >> Table1.A1 = Table2.A2 (+) and
> >> Table3.B3 = Table2.B2 (+)
> >>
> >> When I run this, it gives me the error:
> >> ORA-01417: a table may be outer joined to at most one other table
> >>
> >> Can someone give alternate ways to get this to work (using inline
> >> view, sub queries...)
> >> Really appreciate it!!
> >>
> >> JGP
> >
>
Received on Mon Mar 11 2002 - 15:18:05 CST