jthinson
jthinson

Reputation: 1

Microsoft Access/SQL - Query to combine rows from incomplete tables

I have several data sources, each providing a piece of the truth. In order to understand the full picture, I need to be able to write a query that combines data from all of the sources into one table. The data I have is complicated so I'll provide a simple example to try to understand the concept.

I have Table1 and Table2 given. I need to combine them to fill in the blanks and create a master data source (Table3). Let's assume serial will always be provided.

Table1 (given)

serial1 name1   city1   country1    OS1
123     pc-123                      windows 7
124     pc-124  Tampa   US          Windows XP
125     pc-125  Sydney  AU          Windows 7
126             Tempe   US

Table2 (given)

serial2 name2   city2   country2    OS2
123     pc-123  Atlanta US          
124                     US          Windows XP
125                                 
126     pc-126                      Windows XP


Table3 (what I want to create)

serial  name    city    country     OS
123     pc-123  Atlanta US          windows 7
124     pc-124  Tampa   US          Windows XP
125     pc-125  Sydney  AU          Windows 7
126     pc-126  Tempe   US          Windows XP


Is there a simple union, left join, combination of SQL I can write to execute this? I have been looking thru the forums but couldn't find anything quite like this. Thanks in advance for any help here!

Upvotes: 0

Views: 265

Answers (1)

lc.
lc.

Reputation: 116458

If serial is unique (meaning you have 0..1 rows in each table per serial and they always correlate) and you don't care which value you get on conflicts, you can do something like:

SELECT serial, MAX(name) AS name, MAX(city) AS city, 
    MAX(country) AS country, MAX(OS) AS OS
FROM (
    SELECT serial1 AS serial, name1 AS name, city1 AS city, 
        country1 AS country, OS1 AS OS
    FROM Table1
    UNION ALL
    SELECT serial2 AS serial, name2 AS name, city2 AS city, 
        country2 AS country, OS2 AS OS
    FROM Table2
) AS x
GROUP BY serial

Upvotes: 1

Related Questions