Home » SQL & PL/SQL » SQL & PL/SQL » leftouter join for below scenario
leftouter join for below scenario [message #447824] Thu, 18 March 2010 03:20 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,

I am havinf two tables after doing leftouter join. I need to get below expected output.

 

Table: T1

A	B
--	__

M1	D1
M1	D2
M3	D1
M3	D2




Table: T2

A	B
--	__

M1	D1
M1	D3
M2	D1
M2	D2


Expected Output

A	B
--	__

M1	D1
M1	D2
M1      D3
M3	D1
M3	D2




Any Help really appreciated.


Thanks in advance
Re: leftouter join for below scenario [message #447827 is a reply to message #447824] Thu, 18 March 2010 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Explain what you expect with words not just a result chart.

Regards
Michel
Re: leftouter join for below scenario [message #447830 is a reply to message #447827] Thu, 18 March 2010 03:29 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Pls find below sql statements for DDL and DML.

create table t1 (a varchar2(10), b varchar2(10));

create table t2 as select from t1 where 1=2;


insert into T1 (A, B)
values ('M1', 'D1');
insert into T1 (A, B)
values ('M1', 'D2');
insert into T1 (A, B)
values ('M3', 'D1');
insert into T1 (A, B)
values ('M3', 'D2');
commit;



insert into T2 (A, B)
values ('M1', 'D1');
insert into T2 (A, B)
values ('M1', 'D3');
insert into T2 (A, B)
values ('M2', 'D1');
insert into T2 (A, B)
values ('M2', 'D2');
commit;



Thanks in advance
Re: leftouter join for below scenario [message #447837 is a reply to message #447830] Thu, 18 March 2010 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SELECT A,B FROM T1
UNION 
SELECT A,B FROM T2
where a in (select a from t1);
Re: leftouter join for below scenario [message #447851 is a reply to message #447830] Thu, 18 March 2010 04:32 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post a WORKING test case:
SQL> create table t2 as select from t1 where 1=2;
create table t2 as select from t1 where 1=2
                          *
ERROR at line 1:
ORA-00936: missing expression


Please post FORMATTED (as we did).

Please post ORACLE VERSION.

Please explain requirements WITH WORDS.

"Any Help [to help] really appreciated."

Regards
Michel

[Updated on: Thu, 18 March 2010 04:33]

Report message to a moderator

Previous Topic: Foreign key constraint
Next Topic: Optimization - reuse of a set
Goto Forum:
  


Current Time: Wed Dec 07 14:46:26 CST 2016

Total time taken to generate the page: 0.15915 seconds