Home » SQL & PL/SQL » SQL & PL/SQL » Need a Sql Statement for following scenario (Oracle 10g)
Need a Sql Statement for following scenario [message #350916] Sat, 27 September 2008 11:22 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Hello All,
Here below are the tables which holds the records as show below.
TableA
---------
ID   Col1   Col2
---  ----   ----
123   A       XY
456   B       YZ


TableB
-------
Id	Colb
---	----
123	abc
567	bca


TableC
---------
Id	ColC
---	-----
234	axy
678	pqr


I need to populate a table which holds the records as show below


TableABC

ID	Col1	Col2	Colb	Colc
---	----	----	----	----
123	A	xy	abc	
234				 axy	
456      B	yz	 
567                      bca
678	 			pqr	



I need a sql query which could populate the ID column as shown.
Need your help to get there.

Thanks.
Re: Need a Sql Statement for following scenario [message #350918 is a reply to message #350916] Sat, 27 September 2008 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use FULL OUTER JOIN.

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Need a Sql Statement for following scenario [message #350919 is a reply to message #350916] Sat, 27 September 2008 11:50 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What did YOU try so far?

One way might be using UNION operator whose result would then be used as inline view for another SELECT statement.
Re: Need a Sql Statement for following scenario [message #350935 is a reply to message #350919] Sat, 27 September 2008 15:41 Go to previous messageGo to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Michel >
Test case:

Create table Tbla (Id Number, Col1 Varchar2,Col2 Varchar2);
Create table Tblb (Id Number, Colb Varchar2);
Create table Tblc (Id Number, Colc Varchar2);
Create table tblabc (Id Number,Col1 Varchar2,Col2 Varchar2,Colb Varchar2,Colc Varchar2);

Insert into Tbla
Select(123,'a','XY') From Dual
Union
Select(456,'B','YZ') From Dual;

Insert into Tblb
Select(123,'abc') From Dual
Union
Select(567,'bca') From Dual;

Insert into tblc
Select(234,'axy') From Dual
Union
Select(678,'pqr') From Dual;



Re: Need a Sql Statement for following scenario [message #350937 is a reply to message #350935] Sat, 27 September 2008 16:41 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Your profile indicates your occupation as "Software prof". I am guessing that "prof" stands for professor, so I am surprised that you posted invalid create table and insert statements. I will leave it to you to figure out what is wrong with them and correct them. There are usually multiple ways to do things. As Michel previously suggested, one efficient method would be to use a FULL OUTER JOIN to get your results. You can also use COALESCE to obtain only one id column. You can look up the syntax for both of these in the online documentation. I have posted a partial solution below, replacing some of the code with ... so that you can figure out what goes there. You will learn more by looking up the syntax and figuring it out than if I spoonfeed it to you.

-- test data:
SCOTT@orcl_11g> SELECT * FROM tbla
  2  /

        ID COL1 COL2
---------- ---- ----
       123 a    XY
       456 B    YZ

SCOTT@orcl_11g> SELECT * FROM tblb
  2  /

        ID COLB
---------- ----
       123 abc
       567 bca

SCOTT@orcl_11g> SELECT * FROM tblc
  2  /

        ID COLC
---------- ----
       234 axy
       678 pqr

SCOTT@orcl_11g> SELECT * FROM tblabc
  2  /

no rows selected


-- partial solution:
SCOTT@orcl_11g> INSERT INTO tblabc
  2  SELECT COALESCE ... -- id column
  3  	    ... -- other columns
  4  FROM   ... FULL OUTER JOIN ...
  5  	    FULL OUTER JOIN ...
  6  ORDER  BY ...
  7  /

5 rows created.

SCOTT@orcl_11g> SELECT * FROM tblabc
  2  /

        ID COL1 COL2 COLB COLC
---------- ---- ---- ---- ----
       123 a    XY   abc
       234                axy
       456 B    YZ
       567           bca
       678                pqr

SCOTT@orcl_11g>

Previous Topic: ora-30929 error inspite of having a connect by clause
Next Topic: alter session set schema set schema_name
Goto Forum:
  


Current Time: Sat Dec 03 18:23:54 CST 2016

Total time taken to generate the page: 0.09534 seconds