Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join Problem
Outer Join Problem [message #326332] Wed, 11 June 2008 02:09 Go to next message
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 Go to previous message
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

Previous Topic: Help with this "basic" sql.
Next Topic: ANYDATA datatype
Goto Forum:
  


Current Time: Sun Feb 09 08:51:20 CST 2025