Reputation: 1
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
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