join of group of joins

[edli]

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

[abhaysk]

Its other way

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(+)

[edli]

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

[DaPi]

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.)

[edli]

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

[abhaysk]

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.

[edli]

Don't worry abhaysk.
Thanks for answering anyway.

cheers
edli