Tiger Tiger - 4 years ago 126
SQL Question

SQL How to join two tables to get records in following scenario?

Payments

Year Month Division Department Payments_received_Count
------------------------------------------------------------------------
2016 1 Electric dep1 2
2016 1 Electric dep2 3
2015 1 Electric dep1 1


Divisions

Division Department
--------------------------------
Electric Dep1
Electric Dep2
Electric Dep3


How to join the tables to get the following result?

Year Month Division Department Payments_received_Count
------------------------------------------------------------------------
2016 1 Electric dep1 2
2016 1 Electric dep2 3
2016 1 Electric dep3 0
2015 1 Electric dep1 1
2015 1 Electric dep2 0
2015 1 Electric dep3 0


So for each year and month I have to join the payments table with division table. Please suggest an approach.

I know
CASE WHEN
will help in this scenario, but I have not been able to find the right way.

CASE Payments_received_Count
WHEN null THEN 0
ELSE Payments_received_Count


Thanks

Answer Source

You need a combination of division/department and year/month. First, generate the rows using cross join. Then use left join to get the values:

select ym.year, ym.month, d.division, d.department,
       coalesce(Payments_received_Count, 0) as Payments_received_Count
from divisions d cross join
     (select distinct year, month from payments) ym left join
     payments p
     on d.division = p.division and d.department = p.department and
        ym.year = p.year and ym.month = p.month
order by year desc, month desc, division, department;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download