Using Left join , Union and Right Join to get a desired result

Question

I have two tables namely "CProduct" and "DProduct".Below are the examples:

CProduct :

EffectiveDate   CFund
2014-01-03      0.06
2014-01-03      0.12
2014-01-06      0.11

DProduct :

EffectiveDate   DFund
2014-01-03      0.06
2014-01-06      0.12
2014-01-08      0.09

I want to get a result like below :

EffectiveDate  CFund   DFund
2014-01-03     0.18    0.06
2014-01-06     0.11    0.12
2014-01-08     NULL    0.09

My query is :

SELECT a.EffectiveDate,a.CFund,a.DFund      
FROM (
SELECT t1.EffectiveDate,Sum(t1.CFund) as CFund ,SUM(t2.DFund) as DFund FROM CProduct t1 
LEFT JOIN DProduct t2 ON t1.EffectiveDate = t2.EffectiveDate Group By t1.EffectiveDate
UNION
SELECT t1.EffectiveDate,SUM(t2.CFund) as CFund ,Sum(t1.DFund) as DFund FROM DProduct t1 
LEFT JOIN CProduct t2 ON t1.EffectiveDate = t2.EffectiveDate Group By t1.EffectiveDate
) a

But I am not getting the desired result.

Answer 1

This gets your desired results - not quite sure why the other answerers think joins and COALESCE are so crucial:

SELECT a.EffectiveDate, SUM(a.CFund) AS CFund, SUM(a.DFund) AS DFund
FROM (
    SELECT c.EffectiveDate, c.CFund, NULL AS DFund
    FROM CProduct c
    UNION ALL
    SELECT d.EffectiveDate, NULL AS CFund, d.DFund
    FROM DProduct d
) a
GROUP BY a.EffectiveDate
ORDER BY a.EffectiveDate

In SQL Fiddle, against SQLite (I haven't checked, but should be fine with Access too): http://sqlfiddle.com/#!7/80158/1