Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Twin outer joins

Re: Twin outer joins

From: corey lawson <corey.lawson_at_worldnet.att.com>
Date: Mon, 11 Mar 2002 03:05:48 GMT
Message-ID: <3c8c1e15.30019263@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 Sun Mar 10 2002 - 21:05:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US