-
Multiple Outer joins on same table
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
-
I don't believe that your example properly shows the problem.
I ran the following script and received a proper resultset:
Code:
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:
Code:
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|