-
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
-
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"
-
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
-
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.)
-
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
-
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|