Home » SQL & PL/SQL » SQL & PL/SQL » How to Change Left outer join in ANSI
How to Change Left outer join in ANSI [message #213278] Wed, 10 January 2007 02:57 Go to next message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi All,
I have a SQl query like this.

Select C.a,
C.b,
A.c,
B.d
From First_table A ,
Second_table B,
Third_table C
Where C(+).x = A.x
AND C(+).y = A.y
AND C(+).x = B.x
AND C(+).y = B.y ;

When i run this Query , the oracle ERROR:
"ORA-01417: a table may be outer joined to at most one other table". Comes.


Can you please tell me how i can change this Query in ANSI LEFT OUTER JOIN to give the same result.

Thanks
Rawatd
Re: How to Change Left outer join in ANSI [message #213296 is a reply to message #213278] Wed, 10 January 2007 04:11 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hello,
as a result of misplaced (+) your query does not throw the error you stated, but
Quote:
ORA-00933: SQL command not properly ended

I do not know how to reproduce your error by joins. Maybe you want something like
Select C1.a, C1.b, A.c, B.d
From Third_table C1 left outer join First_table A using (x, y),
Third_table C2 left outer join Second_table B using (x, y)
Where (C1.x = C2.x AND C1.y = C2.y)
OR (C1.x is null and C2.x is null and C1.y is null and C2.y is null);

You should have composite primary key (x, y) on tables A and B.
Then the result will contain inner join of the tables plus ALL combinations of rows from tables A and B which do not have corresponding row in C. Seems like bad idea (design?) to me.

It is also acceptable you have composite primary key (x, y) on table C. Then you get ALL combinations of rows from tables A and B with the same (x, y) which is present in C plus ALL combinations of rows from tables A and B which do not have corresponding row in C.

[Edit: Added last paragraph]

[Updated on: Wed, 10 January 2007 21:19]

Report message to a moderator

Re: How to Change Left outer join in ANSI [message #213312 is a reply to message #213296] Wed, 10 January 2007 04:55 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
does this work for you?

Select first_table.a,
Third_table.b,
first_table.c,
third_table.d
from first_table
left outer join (second_table right outer join third_table on (third_table.x=  second_table.x and
third_table.y=second_table.y)) on (first_table.x=  second_table.x and
first_table.y=second_table.y)

[Updated on: Wed, 10 January 2007 04:55]

Report message to a moderator

Re: How to Change Left outer join in ANSI [message #213363 is a reply to message #213278] Wed, 10 January 2007 07:47 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
In your query :

Select C.a,
C.b,
A.c,
B.d
From First_table A ,
Second_table B,
Third_table C
Where C(+).x = A.x
AND C(+).y = A.y
AND C(+).x = B.x
AND C(+).y = B.y ;
you don't have join from A to B.

Try:

Select C.a,
C.b,
A.c,
B.d
From First_table A ,
Second_table B,
Third_table C
Where B.x = A.x
AND B.y = A.y
AND C(+).x = A.x
AND C(+).y = A.y ;

HTH

Previous Topic: Decode statement
Next Topic: Get file from internet
Goto Forum:
  


Current Time: Sat Dec 10 07:03:52 CST 2016

Total time taken to generate the page: 0.08179 seconds