OraFAQ Forum: SQL & PL/SQL » leftouter join for below scenario

Home » SQL & PL/SQL » SQL & PL/SQL » leftouter join for below scenario

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

leftouter join for below scenario [message #447824]

Thu, 18 March 2010 03:20

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

Report message to a moderator

Re: leftouter join for below scenario [message #447827 is a reply to message #447824]

Thu, 18 March 2010 03:23

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: leftouter join for below scenario [message #447830 is a reply to message #447827]

Thu, 18 March 2010 03:29

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

Report message to a moderator

Re: leftouter join for below scenario [message #447837 is a reply to message #447830]

Thu, 18 March 2010 03:39

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);

Report message to a moderator

Re: leftouter join for below scenario [message #447851 is a reply to message #447830]

Thu, 18 March 2010 04:32

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 11 09:01:57 CDT 2025