1

I have one table that I created to summarized the data and am wanting to use that for my master table. I need to join multiple tables into this one master table but cannot get my 2017 data into it. When I do a LEFT JOIN it adds another column for the Wireless Rev. How do I input my 2017 data where the "nans" are so that it replaces the "nans"? Example of tables:

Table1

State    Year    Wireline    Wireless
-------------------------------------
TENN.    2017    120         NAN
TEXAS    2017    255         NAN
TENN.    2018    182         55
TEXAS    2018    222         120

Table2

State    Year    Wireless
-------------------------------------
TENN.    2017    222
TEXAS    2017    431

I have already tried a left join but it creates another column next to the existing wireless:

SELECT Table1.*,Table2.Wireless
FROM Table1    
LEFT JOIN Table2
ON Table1.State = Table2.State
AND Table1.Year = Table2.Year

This gives me:

State    Year    Wireline    Wireless    Wireless
--------------------------------------------------
TENN.    2017    120         NAN         222
TEXAS    2017    255         NAN         431  
TENN.    2018    182         55          0 
TEXAS    2018    222         120         0

I am hoping to get:

State    Year    Wireline    Wireless
-------------------------------------
TENN.    2017    120         222
TEXAS    2017    255         431
TENN.    2018    182         55
TEXAS    2018    222         120
Michael Ryan
  • 73
  • 5

1 Answers1

1

Maybe something like this?

SELECT Table1.State,
       Table1.Year,
       Table1.Wireline,
       COALESCE(Table1.Wireless, Table2.Wireless) AS Wireless
FROM Table1    
LEFT JOIN Table2
ON Table1.State = Table2.State
AND Table1.Year = Table2.Year
SQLRaptor
  • 671
  • 4
  • 14
  • Giving me "ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Undefined function 'COALESCE' in expression. (-3102) (SQLExecDirectW)")" I am doing this in Python btw – Michael Ryan May 20 '19 at 20:25
  • I essentially just want to insert those two values in place of those nans – Michael Ryan May 20 '19 at 20:39
  • if you are using access, try to replace the "COALESCE" keyword with "Nz" which is the access alternative. https://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql – SQLRaptor May 20 '19 at 21:54