Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql table merge

Posted on 2014-07-15
11
Medium Priority
?
183 Views
Last Modified: 2014-07-31
I have two tables with the values:

Table 1:

a   b    c
n   -     1
m  -     3
o   -     4


Table 2:

a   b   c
n   1   -
m  2   -

I want to create table 3, based on the identity of the string in column "a" between tables 1 and 2
and not omitting any rows of Table 1:

Table 3:

a   b   c
n   1   1
m  2   3
o   -    4

What sql will do this?
0
Comment
Question by:code4
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40198402
I think this may be what you are looking for:
select  /* INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table */

        t1."a"
      , coalesce(t1."b",t2."b") "b"
      , coalesce(t1."c",t2."c") "c"
from Table1 t1
left join Table2 t2 on t1."a" = t2."a"
;

Open in new window

but this also matches the expected result above. (i.e. it isn't clear if you want Table1.b to overrule Table2.b and so on)
select  /* INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table */

        t1."a"
      , t2."b"
      , t1."c"
from Table1 t1
left join Table2 t2 on t1."a" = t2."a"
;

Open in new window

Regarding the select into I'll leave that to you, see:
http://www.postgresql.org/docs/9.1/static/sql-selectinto.html
**PostgreSQL 9.3.1 Schema Setup**:

    
    
    CREATE TABLE Table1
    	("a" varchar(1), "b" int, "c" int)
    ;
    	
    INSERT INTO Table1
    	("a", "b", "c")
    VALUES
    	('n', null, 1),
    	('m', null, 3),
    	('o', null, 4)
    ;
    
    CREATE TABLE Table2
    	("a" varchar(1), "b" int, "c" int)
    ;
    	
    INSERT INTO Table2
    	("a", "b", "c")
    VALUES
    	('n', 1, null),
    	('m', 2, null)
    ;

**Query 1**:

    select  /* INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table */
    
            t1."a"
          , coalesce(t1."b",t2."b") "b"
          , coalesce(t1."c",t2."c") "c"
    from Table1 t1
    left join Table2 t2 on t1."a" = t2."a"
    

**[Results][2]**:
    
    | A |      B | C |
    |---|--------|---|
    | m |      2 | 3 |
    | n |      1 | 1 |
    | o | (null) | 4 |


**Query 2**:

    select  /* INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table */
    
            t1."a"
          , t2."b"
          , t1."c"
    from Table1 t1
    left join Table2 t2 on t1."a" = t2."a"
    

**[Results][3]**:
    
    | A |      B | C |
    |---|--------|---|
    | m |      2 | 3 |
    | n |      1 | 1 |
    | o | (null) | 4 |



  [1]: http://sqlfiddle.com/#!15/25921/4

Open in new window

0
 
LVL 22

Expert Comment

by:earth man2
ID: 40198678
Do you want to omit non matching rows in table2 ? If not then use FULL OUTER JOIN instead.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40200251
Is it possible to have values like?
Table1
n   5  1
m   -  3
o   -   4
Table2
n   1   -
m  2   -
If so, what would be the expected results for table3?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:code4
ID: 40200314
Table 1 never has values in column 2.

I want these values to be filled from the matches in table 2,
and if none are found in table 2, to be left unfilled.
Table 3 should have the same number of rows as table 1.

Thanks
0
 
LVL 32

Expert Comment

by:awking00
ID: 40200338
Then PortletPaul's solution should work.
0
 

Author Comment

by:code4
ID: 40200845
PortletPaul: Thanks, but this did not work.
It created duplicate entries in table 3.

I need table 3 to have exactly the same number of rows as table 1.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40200856
your sample data does not demonstrate this issue,
could you provide more details please?

all we know about your tables is what you put in the question
0
 
LVL 22

Expert Comment

by:earth man2
ID: 40201331
SELECT DISTINCT ...
0
 
LVL 22

Expert Comment

by:earth man2
ID: 40201337
I guess if same number of rows is essential then use a correlated subquery.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40201371
we might use distinct or maybe a correlated query: who really knows until we see more information

my crystal ball fell off the table and cracked...
0
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 40201898
Scenarios producing duplicates would include having duplicates in either or both tables. What do the following queries produce (using your data that produced the duplicates)?
select a, b, c, count(*) from table1 group by a, b, c having count(*) > 1;
select a, b, c, count(*) from table2 group by a, b, c having count(*) > 1;
If either or both of these queries return records, then there are duplicates in one or both tables and using distinct would remove them but, if the duplicates are in table1, you will not get one row for each row in that table using the suggested query.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question