Home » SQL & PL/SQL » SQL & PL/SQL » Merging Tables through SQL (10g)
Merging Tables through SQL [message #309059] Wed, 26 March 2008 06:07 Go to next message
orabiswa
Messages: 4
Registered: March 2008
Location: INDIA
Junior Member
TABLE A
------------
COL1      COL2
----      ----
100       A
101       D

TABLE B
------------
COL1      COL3
----      ----
100       B
103       C

RESULT ANT TABLE:

TABLE AB
----------

COL1      COL2    COL3
----      ----    -----

100       A        B
101       D       -- 
103      ---       C


Can any one help me out to get the resultant table(TABLE AB), from two source tables TABLE A and TABLE B by ORACLE SQL


[TITLE modified by LF; was "tbales". ADDED [code] tags]

[Updated on: Fri, 28 March 2008 00:50] by Moderator

Report message to a moderator

Re: Merging Tbales through SQL [message #309065 is a reply to message #309059] Wed, 26 March 2008 06:24 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Search for JOIN.And next time format your code.Read forum guidelines, the very first post in the forum.


regards,
Re: Merging Tbales through SQL [message #309075 is a reply to message #309059] Wed, 26 March 2008 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
More precisely:
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Read Joins

Regards
Michel
Re: Merging Tables through SQL [message #309076 is a reply to message #309065] Wed, 26 March 2008 06:38 Go to previous messageGo to next message
orabiswa
Messages: 4
Registered: March 2008
Location: INDIA
Junior Member
Sorry,

Hi,

TABLE A
------------
COL1 COL2
---- ----
100 A
101 D

TABLE B
------------
COL1 COL3
---- ----
100 B
103 C

RESULT ANT TABLE:

TABLE AB
----------

COL1 COL2 COL3
---- ---- -----

100 A B
101 D ---
103 --- C


Can any one help me out to get the resultant table(TABLE AB), from two source tables TABLE A and TABLE B by ORACLE SQL or PLsql

I think I was not so clear to you

RESULT ANT TABLE will be

TABLE AB
----------

COL1 COL2 COL3 IN_TAB_A IN_TAB_A IN_BOTH
---- ---- ----- ------- --------- -------
100 A B 0 0 1
101 D --- 1 0 0
103 -- C 0 1 0

thanks and regards
Re: Merging Tables through SQL [message #309081 is a reply to message #309076] Wed, 26 March 2008 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is still not formatted. Read carefully the guidelines.

Post what you already tried.
You also got more help if you post a test case: create table and insert statements.

Regards
Michel


[Updated on: Thu, 27 March 2008 01:41]

Report message to a moderator

Re: Merging Tables through SQL [message #309287 is a reply to message #309081] Thu, 27 March 2008 00:57 Go to previous messageGo to next message
orabiswa
Messages: 4
Registered: March 2008
Location: INDIA
Junior Member
Boss,
I don't have enough time format my question.
My concern is the technology on which I am working, I got stuck, that is why asked you guys

If you think format is more important that knowledge,

then it ok

if you can solve it then it is ok or byyy.
Re: Merging Tbales through SQL [message #309288 is a reply to message #309059] Thu, 27 March 2008 00:59 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I don't have enough time format my question.
Then we don't have any time to answer.

You're On Your Own (YOYO)!
Re: Merging Tables through SQL [message #309564 is a reply to message #309076] Thu, 27 March 2008 22:14 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
COL1 COL2 COL3 IN_TAB_A IN_TAB_A IN_BOTH
---- ---- ----- ------- --------- -------
100   A    B      0       0        1      
101   D   ---     1       0        0
103   --   C      0       1        0


Are you asking to have AND operation of the results of two table
Please clarify.

Re: Merging Tables through SQL [message #309568 is a reply to message #309564] Thu, 27 March 2008 23:08 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
SELECT
 DECODE(Table1.col1,NULL,Table2.col1,Table1.col1) AS Col1,
 col2,col3,
 DECODE(col2,NULL,0,1) IN_TAB_A,
 DECODE(col3,NULL,0,1) IN_TAB_B,
 CASE
   WHEN DECODE(col2,NULL,0,1)=1 AND DECODE(col3,NULL,0,1)=1  
   THEN 1
   ELSE 0 
 END AS IN_TAB_BOTH     
FROM 
  Table1 FULL outer join Table2 ON Table1.col1=Table2.col1
Previous Topic: date query
Next Topic: Pick records from different group.
Goto Forum:
  


Current Time: Tue Dec 06 08:23:23 CST 2016

Total time taken to generate the page: 0.09318 seconds