Multiple Outer joins on same table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Multiple Outer joins on same table

  1. #1
    Join Date
    Sep 2008
    Posts
    8

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Sep 2008
    Posts
    8
    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
  •  



Click Here to Expand Forum to Full Width