Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join Problem
Outer Join Problem [message #326332] |
Wed, 11 June 2008 02:09  |
gauravsunil
Messages: 68 Registered: August 2007 Location: bhubaneswar
|
Member |
|
|
Hi Experts,
I want to do outer join on two tables:
create table sunil1234
(data1 NUMBER NOT NULL,data2 VARCHAR2(2),
data3 NUMBER(10) NOT NULL,
data4 FLOAT(126),
data5 NUMBER NOT NULL,
data6 number);
create table sunil123456(data7 NUMBER NOT NULL,data1 NUMBER NOT NULL,
data2 VARCHAR2(2),data8 NUMBER(10) NOT NULL,data9 FLOAT(126),data5 NUMBER NOT NULL,
data6 number);
insert into sunil1234 values(1,'A',123,123.5,091108,4);
insert into sunil1234 values(2,'B',223,223.5,091109,5);
insert into sunil123456 values(5,'1','A',8,111.5,091108,4);
insert into sunil123456 values(5,'2','B',9,112.5,091109,5);
insert into sunil123456 values(5,'3','C',10,113.5,091106,6);
Commit;
I am not able to get the data i suppose to get...
select * from sunil1234;
DATA1 DATA2 DATA3 DATA4 DATA5 DATA6
---------------------- ----- ---------------------- ---------------------- ---------------------- ----------------------
1 A 123 123.5 91108 4
2 B 223 223.5 91109 5
2 rows selected
select * from sunil123456;
DATA7 DATA1 DATA2 DATA8 DATA9 DATA5 DATA6
---------------------- ---------------------- ----- ---------------------- ---------------------- ---------------------- ----------------------
5 1 A 8 111.5 91108 4
5 2 B 9 112.5 91109 5
5 3 C 10 113.5 91106 6
3 rows selected
SELECT * FROM sunil1234 a,sunil123456 b WHERE a.data2=b.data2(+);
DATA1 DATA2 DATA3 DATA4 DATA5 DATA6 DATA7 DATA1 DATA2 DATA8 DATA9 DATA5 DATA6
---------------------- ----- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----- ---------------------- ---------------------- ---------------------- ----------------------
1 A 123 123.5 91108 4 5 1 A 8 111.5 91108 4
2 B 223 223.5 91109 5 5 2 B 9 112.5 91109 5
2 rows selected
I am expecting 3 rows in the outer join select statement.
Please look into it.
Thanks and Regards,
Sunil
|
|
|
Re: Outer Join Problem [message #326336 is a reply to message #326332] |
Wed, 11 June 2008 02:18  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Do the outer join the other way round if you want to get all the rows from SUNLIN123456 and data from SUNLIL1234 if it exists.
SELECT * FROM sunil1234 a,sunil123456 b WHERE a.data2(+)=b.data2;
[remember to actually change the select. D'oh]
[Updated on: Wed, 11 June 2008 02:19] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sun Feb 09 08:51:20 CST 2025
|