join of group of joins
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: join of group of joins

  1. #1
    Join Date
    Jun 2003
    Posts
    47

    join of group of joins

    Hi,

    I have this in t-sql:

    table at1(id1,name)
    at2(id2,name,id1,id4)
    at3(id3,name,id_t)
    at4(id4,name,id_t,id5)
    at5(id5,name)


    I have this join

    select *
    from at1 right outer join at2 on at1.id1=at2.id2
    right outer join
    (at3 inner join at4 on at4.id_t=at3.id_t
    inner join at5 on at5.id5=at4.id5) on at2.id4=at4.id4


    how is this done in Oracle?
    Is this right???

    select *
    from at1,at2,at3,at4,at5
    where at2.id1=at1.id1(+)
    and at3.id_t=at4.id_t
    and at5.id5=at4.id5
    and at4.aid4=t2.id2(+)

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Its other way

    Code:
    select *
     from at1,at2,at3,at4,at5
     where at1.id1=at2.id1(+)
     and at3.id_t=at4.id_t
     and at5.id5=at4.id5
     and at2.id4=at4.id2(+)
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Jun 2003
    Posts
    47
    Hi,

    well I was quite sure the side was right and was wondering if that was the way.And I still think the side is right.Cuold u have another look ate this.

    thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    edli, yours looks OK to me (apart from typos), the question is: will Oracle run it? outer joins are not what it does best. Why not try it? The rule is that the (+) goes on the side where you need to "add" unmatched rows.

    (BTW, if I could I always wrote the joins with INNER's first then then LEFT OUTER's - you usually don't need any parentheses that way.)

  5. #5
    Join Date
    Jun 2003
    Posts
    47
    Hi,

    Thanks DaPi.
    I have tried to run it and it runs.But as my tables are empty I could not try (for sure) that it gave the right result.
    As for the (+) thing I know it goes on that side.
    But as I am bringing some sp from t-sql to oracle I have the queries
    in t-sql and in T-sql u can do much like joining groups in different ways and I don't know if that make any difference in oracle.

    e.g

    select *
    from t1 inner join t2
    inner join t3 on t2.id3=t3.id3
    on t1.id1=t3.id1
    left outer join t4
    inner join t5 on t4.id4=t5.id4
    on t2.id2=t4.id2 and t3.id3=t5.id3;


    in this case as in the previos it joins groups of joins and is very well specified.
    So I can not escape from outer joins.Or I can??

    thanks again
    edli

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DaPi
    edli, yours looks OK to me (apart from typos),
    Well yes..

    Its been quite some time i have left SQL Server tech...and now Left outer join, right outer join is confusing me...

    edli : Sorry 4 wrong answer.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jun 2003
    Posts
    47
    Don't worry abhaysk.
    Thanks for answering anyway.

    cheers
    edli

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