| inner join within an outer join [message #21377] |
Tue, 30 July 2002 14:19  |
joins
Messages: 2
Registered: July 2002
|
Junior Member |
|
|
I know someone had posted similar question before, however all the suggestions posted don't answer my question. So here it goes again:
I've the following SQL-92 standard query:
select T1.A from T1 left outer join (T2 inner join T3 on T2.B = T3.B) on T1.A = T3.A
Trying to run the following Oracle native sql does not return the correct result:
select T1.A from T1, T2, T3 where T2.B = T3.B and T1.A = T3.A(+)
Oracle seems to insist on doing the outer join first before the inner join. Anyway, 2 suggestions posted to the board involved: 1) create a VIEW for the inner join part and use the VIEW for outer join; 2) rewrite the inner join as derived table: select T1.A from T1, (select T3.A from T2, T3 where T2.B = T3.B) T where T1.A = T.A(+).
What I'm looking for is how to rewrite the Oracle native query, without using the 2 suggestions (no views, no use of derived table) to get the correct result. Can anyone help me?
|
|
|
|
|
|
| Re: inner join within an outer join [message #21386 is a reply to message #21377] |
Wed, 31 July 2002 09:09  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
If you have T1 outer join to T2 and T2 outer joins to T3 then you need to do as you say in (2) i.e. create an inline view.
T1----(+)T2-----(+)T3
select t1.x, v.y, v.z from t1, (select t2.y, t3.z from
t2, t3 where t2.joincol=t3.joincol (+)) v and t1.joicol=v.joincol (+);
If you have T1 outer joins to T2 and T1 outer joins to T3 then you don't have a problem.
T1----(+)T2
+-----(+)T3
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
