CODEWITHSUNDEEP
sqlselectjoin
Pedrusco
Pedrusco

Reputation: 31

Combining two SQL tables with common columns

I have two tables that I want to combine into one. They have some common columns, but I can't figure out how to combine them properly.

Samples:

Table A

Month | Year | Costumer_ID | Total_A |
--------------------------------------
  10  | 2014 |    222      |   50    | 
   1  | 2015 |    111      |  100    |
   2  | 2015 |    111      |  200    |
   4  | 2015 |    222      |  250    |

Table B

Month | Year | Costumer_ID | Total_B |
--------------------------------------
  1   | 2015 |    111      |   50    |
  2   | 2015 |    333      |  100    |
  4   | 2015 |    222      |  250    |

Desired Result

Month | Year | Costumer_ID | Total_A | Total_B |
------------------------------------------------
 10   | 2014 |    222      |   50    |     0   |
  1   | 2015 |    111      |  100    |    50   |
  2   | 2015 |    111      |  200    |     0   |
  2   | 2015 |    333      |    0    |   100   |
  4   | 2015 |    222      |  250    |   250   |

The logic is the following:

In the table A for each month in each year if the Total_A of a Client is not zero, there will be a record in the table. Therefore, not all the months of each year have a record for each client.

Table B works the same way as table A but with Total_B different from Total_A. Therefore, for a specific month and year, a client may have a record in just one table, in both or in none of them.

I want to generate a table containing records for each client for each month in each year. If there is a corresponding record in Table A or Table B, Total_A and Total_B of each table will be shown in the resulting table. If there is not a record for that specific client in that specific month and year in any of the tables, the Total_A or/and Total_B will be zero in the resulting table.

Upvotes: 1

Views: 577

Answers (2)

jarlh
jarlh

Reputation: 44795

Do a FULL OUTER JOIN, use COALESCE to get 0 instead of NULL.

select coalesce(ta.Month, tb.Month) as Month,
       coalesce(ta.Year, tb.year) as Year,
       coalesce(ta.Costumer_ID, tb.Costumer_ID) as Costumer_ID,
       coalesce(ta.Total_A,0) as Total_A,
       coalesce(tb.Total_B,0) as Total_B
from tableA ta FULL outer join tableB tb
  on  ta.year = tb.year
  and ta.month = tb.month
  and ta.Costumer_ID = tb.Costumer_ID

Upvotes: 1

Mureinik
Mureinik

Reputation: 312136

This looks like a full outer join. The 0s can be generated with coalesce:

SELECT COALESCE(a.month, b.month) AS month, 
       COALESCE(a.year, b.year) AS year, 
       COALESCE(a.customerid, b.customerid) AS customerid, 
       COALESCE(total_a, 0) AS total_a, 
       COALESCE(total_b, 0) AS total_b
FROM   a
FULL OUTER JOIN b ON a.month = b.month AND
                     a.year = b.year AND
                     a.customerid = b.customerid

Upvotes: 3

Related Questions