join two identical table structures with different data

Question

Edit: After attempting the COALESCE method, I'm now seeing an issue where the data is repeating itself with the same data for each wattage category. Column 2 is wattage.

I've created two temp tables, both with the exact same table structure. In these tables, there are multiple columns that could have the same values, and then a few value columns that will have different numbers. Some of these will be NULL in one column, and not null in another. I want to get all the values together, and on rows with the same site and plant I'd like the values joined.

Here is an example of what the two tables could look like and the result I'd expect

TABLE1:

SITE      PLANT          VALUE_1           VALUE 2
S1        P1             54                66
S1        P2             43                43

TABLE 2:

SITE      PLANT         VALUE_1           VALUE_2
S1        P1            33                43
S2        P1            34                22

RESULT:

SITE      PLANT         t1_VALUE_1    t1_VALUE_2    t2_VALUE_1      t2_VALUE2
S1        P1            54            66            33              43
S1        P2            43            43            NULL            NULL
S2        P1            NULL          NULL          34              22

My original thoughts would be a full join. However, this doesn't work because in your select statement you must specify where to grab the columns from, like site and plant; but to select both t1.site and t2.site would generate two columns. The closest thing I got was the query below, however anytime there is a result in S2 that has a site and plant not in S1, you receive null values for S1 and S2.

SELECT t1.Site, t1.Plant, t1.Value_1, t1.Value_2, t2.Value_1, t2.Value_2 
FROM table1 t1 
FULL JOIN table2 t2 
ON t1.site = t2.site
AND t1.plant = t2.plant

Answer 1

Two tricks are needed to complete this query. The first is a FULL JOIN. A full join will allow you to combine both tables, and insert nulls in either table when you don't match the join condition. The 2nd is COALESCE, which will allow you take the plant and site from whichever of the tables provides a record for this row in the results.

SELECT Coalesce(t1.Site,t2.Site) As Site, COALESCE(t1.Plant, t2.Plant) As Plant,
    t1.Value_1 As t1_Value_1, t1.Value_2 As t1_Value_2,
    t2.Value_1 As t2_Value_1, t2.Value_2 As t2_Value_2
FROM Table1 t1
FULL JOIN Table2 t2 ON t1.Site = t2.Site AND t1.Plant = t2.Plant

Answer 2

You can use coalesce. It will return the first non null value from the parameters.

SELECT coalesce(t1.Site, t2.Site) as Site, coalesce(t1.Plant, t2.Plant) as Plant,