CODEWITHSUNDEEP
sqlms-access
Jeebwise
Jeebwise

Reputation: 177

Combining data from two partially overlapping access tables

I have two tables with the same number of columns

Uncombined

This is a sample of the kind of output that can happen. As you can see there is some overlap(193 is in both). I have tried a left join, right join, and both using a union but I can't get all the information to exist as one. The best I can get is this

Combined

How do I combine the ProdSys columns into one(if they match) but keep the two other columns the way they are.

SELECT NewCoAvgCalcFull.ProdSys, NewCoAvgCalcPartial.ProdSys, NewCoAvgCalcFull.CO_Average, NewCoAvgCalcPartial.CO_Average 
FROM NewCoAvgCalcFull 
LEFT JOIN NewCoAvgCalcPartial 
ON NewCoAvgCalcFull.ProdSys = NewCoAvgCalcPartial.ProdSys 
UNION 
SELECT NewCoAvgCalcFull.ProdSys, NewCoAvgCalcPartial.ProdSys, NewCoAvgCalcFull.CO_Average, NewCoAvgCalcPartial.CO_Average 
FROM NewCoAvgCalcFull 
RIGHT JOIN NewCoAvgCalcPartial 
ON NewCoAvgCalcFull.ProdSys = NewCoAvgCalcPartial.ProdSys;

Upvotes: 1

Views: 1632

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Is this what you want?

SELECT nacf.ProdSys, nacf.CO_Average as full_co_average, NULL as partial_co_average
FROM NewCoAvgCalcFull as nacf
UNION ALL
SELECT nacp.ProdSys, NULL, nacp.CO_Average
FROM NewCoAvgCalcPartial as nacp;

EDIT:

The above creates two rows when there is a match in each table. Perhaps you really want:

SELECT nacf.ProdSys, nacf.CO_Average as full_co_average,
       nacp.CO_Average as partial_co_average
FROM NewCoAvgCalcFull as nacf LEFT JOIN
     NewCoAvgCalcPartial as nacp
     on nacp.ProdSys = nacf.ProdSys
UNION ALL
SELECT nacp.ProdSys, NULL, nacp.CO_Average
FROM NewCoAvgCalcPartial as nacp
WHERE NOT EXISTS (SELECT 1
                  FROM NewCoAvgCalcFull as nacf
                  WHERE nacf.ProdSys = nacp.ProdSys
                 );

Upvotes: 2

Related Questions