Home » SQL & PL/SQL » SQL & PL/SQL » Result as per Double Outer Join ? (10.2.0)
Result as per Double Outer Join ? [message #618587] Mon, 14 July 2014 02:23 Go to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
Dear All,

Here are tables and requirement:


 create table t1 (sno number(3), incoming varchar2(4));

 
 
 insert into t1 values (1, 'A');
 insert into t1 values (2, 'B');
 insert into t1 values (3, 'C');
 insert into t1 values (4, 'D');

 
 
 
 create table t2 (sno number(3), outgoing varchar2(4));

 
 
 insert into t2 values (1, 'E');
 insert into t2 values (2, 'F');
 insert into t2 values (3, 'G');
 insert into t2 values (5, 'H');




Required

Quote:
Sno, incoming, outgoing
1, A, E
2, B, F
3, C, G
4. D, (null)
5, (null), H




What i have done and achieved is the following query ran into oracle reports with matrix and got my desired output
select sno, 'Inco', incoming
from t1
union all
select sno, 'Ootgo', outgoing
from t2



How is it possible in SQL?
Re: Result as per Double Outer Join ? [message #618589 is a reply to message #618587] Mon, 14 July 2014 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read Join, and focus on "Full outer join" point.

Re: Result as per Double Outer Join ? [message #618590 is a reply to message #618587] Mon, 14 July 2014 02:27 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
FULL OUTER JOIN is the syntax you need.
Re: Result as per Double Outer Join ? [message #618592 is a reply to message #618590] Mon, 14 July 2014 03:01 Go to previous message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
select decode(t1.sno, null, t2.sno, t1.sno), 
t1.incoming, 
t2.outgoing 
from t1 full outer join t2 on t1.sno=t2.sno

Done.
OUTER JOIN command was not in my knowledge Sad

Thanks for the guidance for new learning.

Previous Topic: how to get script of table in sql plus
Next Topic: what does table.column = table.column(+) refer to
Goto Forum:
  


Current Time: Thu Mar 28 19:52:33 CDT 2024