Multiple Outer joins on same table

[texan]

Hi all, i have a question on Outer join. Is that possible to make a multiple outer joins on one table?

Here is my test case:

I have multiple join condition in my query. I am using 6 tables and 5 join conditions.
Table A a,
Table B b,
Table C c,
Table D d,
Table E e,
Table F f
Where a.id = b.id (+) and
b.id = c.id (+) and
a.id = d.id (+) and
d.id = e.id and
a.id = f.id (+)
When I am doing this I got an error like single table defined multiple outer joins.

Can some one help me on this?

I can do this in other way like writing In-line views but i want to know is this possible or not. I am Using ORACLE 10g.


Thanks a lot for replying

[chrisrlong]

I don't believe that your example properly shows the problem.

I ran the following script and received a proper resultset:

create table a (id number);
create table b (id number);
create table c (id number);
create table d (id number);
create table e (id number);
create table f (id number);

insert into a values (1);
insert into b values (1);
insert into c values (1);
insert into d values (1);
insert into e values (1);
insert into f values (1);

select * from 
a,
b,
c,
d,
e,
f
Where a.id = b.id (+) and
b.id = c.id (+) and
a.id = d.id (+) and
d.id = e.id and
a.id = f.id (+)

That is because you are outer-joining (+) each table only once.

Note that the following SQL will produce the error:

select * from 
a,
b,
c,
d,
e,
f
Where a.id = b.id (+) and
b.id = c.id (+) and
a.id = d.id (+) and
e.id = d.id(+) and
a.id = f.id (+)

It is very rare that you will need to outer-join to more than one table. In such cases, you will need to, as you stated, re-write the query to put the two tables that you wish to outer-join to into an inline view and then outer-join to the results.

I would first check the logic of your statement, because it is more likely that there is a logical error in what you are trying to do.

Two more points of note from your original example, which may just be because you threw it together:

d.id = e.id <-- This will eliminate what you tried to accomplish in the previous predicate:
a.id = d.id (+)

Also note that if all these tables shared the same ids, then the best way to write the WHERE clause would be:
b.id (+)= a.id AND
c.id (+)= a.id AND
d.id (+)= a.id AND
e.id (+)= a.id AND
f.id (+)= a.id

This gives the optimizer more options and more correctly reflects what you are trying to do, assuming a is the driving table and the rest are children of a.

[texan]

Hi Chris, thanks for your reply and i will do that necessary modifications for better query optimization.

I will use In-line view for the table that needs multiple outer join. Do you have any other ways otherthat in-line views? in this Scenario?

Thanks in advance.