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.

Two questions: 1. Can there be duplicate dates in DProduct? 2. Can there be dates in CProduct that don't exist in DProduct? — Thorsten Kettner, Jan 21 '15 at 08:34

Damien_The_Unbeliever · Answer 1 · 2015-01-21T08:22:41.620

3

Should just be some subqueries and a full outer join. Not sure why you think a UNION is required (especially because that eliminates duplicate rows):

SELECT
    COALESCE(t1.EffectiveDate,t2.EffectiveDate) as EffectiveDate,
    t1.Total,
    t2.Total
FROM
    (select EffectiveDate,SUM(CFund)
    from CProduct
    group by EffectiveDate) as t1(EffectiveDate,Total)
        full outer join
    (select EffectiveDate,SUM(DFund)
    from DProduct
    group by EffectiveDate) as t2(EffectiveDate,Total)
        on
            t1.EffectiveDate = t2.EffectiveDate

edited Jan 21 '15 at 08:22

answered Jan 21 '15 at 07:35

Damien_The_Unbeliever

234,701
27
340
448

is there any way without using COALESCE function ?? – techV Jan 21 '15 at 07:51
@vivek Why do you want to avoid coalesce? – Taemyr Jan 21 '15 at 08:14
@Taemyr because i need to use it with vba (MS- ACCESS) and this is sql function which will not work in vba. – techV Jan 21 '15 at 08:17
1

@vivek - You're allowed up to 5 tags on a question and you've only used 3, but you thought it was more important to apply 2 SQL Server tags than to include [tag:ms-access]? – Damien_The_Unbeliever Jan 21 '15 at 08:19
@vivek http://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql – Taemyr Jan 21 '15 at 08:21
@Damien_The_Unbeliever I think you are missing group by clauses in your inner querries. – Taemyr Jan 21 '15 at 08:22
@Taemyr dnt know why but even after\ using Nz in vba query it is giving error as "Syntax Error in From clause".. can you look at it. – techV Jan 21 '15 at 09:28
@Taemyr I have already added group by clause. but still it is not working in vba and in sql it is working as intended. – techV Jan 21 '15 at 09:29
@vivek try replacing `SUM(CFund)` with `SUM(CFund) as Total`, and `as t1(EffectiveDate,Total)` with `as t1`. And similarilly for the second subquery. – Taemyr Jan 21 '15 at 09:39
@Taemyr I have tried but now it is giving me "Suyntax Error in union query" as i have replaced "full outer join" with "Union all".. i think full outer join not work with vba. – techV Jan 21 '15 at 09:43
@vivek Full outer join and union all does completely different things. – Taemyr Jan 21 '15 at 09:50
@Taemyr Ok.. so is there any alternate for full outer join in vba query? – techV Jan 21 '15 at 09:55
@vivek The query in my answer does, by hapstance, not use outer join. See Thorstens answer for an approach to emulating an outer join as the union of a left and a right join. Thorstens approach works when there are no duplicate rows, in this case that holds because he does the aggregation in the subqueries. – Taemyr Jan 21 '15 at 09:58

score 2 · Accepted Answer · answered Jan 21 '15 at 21:53

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

Thorsten Kettner · Answer 3 · 2015-01-21T09:46:30.660

You are using the opposed outer joins with UNION in order to mimic a FULL OUTER JOIN. This is okay, but not necessary as SQL Server 2008 (and 2005, too, for that matter) feature full outer joins.

Your problem, however, is more fundamental. You are joining all records from CProduct and DProduct and then build sums. So say for date 2014-01-01 there are two records in CProduct and three records in DProduct. Your join gives you six records (2x3). Then you build your sums, thus considering DProduct values two-fold and CProduct entries three-fold.

Having said this, you don't want to join each single CProduct record with each single DProduct record by date. You want to join the sums per date. I.e. aggregate first, then join.

select 
  coalesce(c.effectivedate, d.effectivedate) as effectivedate,
  coalesce(c.sumfund,0) as cfund, 
  coalesce(d.sumfund,0) as dfund
from 
  (select effectivedate, sum(cfund) as sumfund from cproduct group by effectivedate) c
full outer join 
  (select effectivedate, sum(dfund) as sumfund from dproduct group by effectivedate) d
on c.effectivedate = d.effectivedate;

Without FULL OUTER JOIN:

select 
  c.effectivedate,
  c.sumfund as cfund, 
  d.sumfund as dfund
from 
  (select effectivedate, sum(cfund) as sumfund from cproduct group by effectivedate) c
left outer join 
  (select effectivedate, sum(dfund) as sumfund from dproduct group by effectivedate) d
on c.effectivedate = d.effectivedate
union
select 
  d.effectivedate,
  c.sumfund as cfund, 
  d.sumfund as dfund
from 
  (select effectivedate, sum(cfund) as sumfund from cproduct group by effectivedate) c
right outer join 
  (select effectivedate, sum(dfund) as sumfund from dproduct group by effectivedate) d
on c.effectivedate = d.effectivedate;

OP requested NULL when no entries for the date existed, so the last two coalesce functions are unecesarry. — Taemyr, Jan 21 '15 at 09:32
@Thorsten can you please provide me equivalent working vba query as above query even after replacing COALESCE by Nz, it is giving Syntax Error in From Clause in vba. dnt know why.But working fine in sql. — techV, Jan 21 '15 at 09:34
@vivek: VBA? MS-Access? You had tagged your request SQL-Server 2008. I think MS-Access doesn't support full outer joins, so I add a mimicking query (though I think you should have been able to build this yourself with the information given). — Thorsten Kettner, Jan 21 '15 at 09:43
@Thorsten yes it was my mistake. I apologize for this. I have corrected it. i am trying it now. — techV, Jan 21 '15 at 09:49
@ThorstenKettner I am not very much sure if "left outer join" works with vba or not. — techV, Jan 21 '15 at 09:52
@vivek: I'm not sure either. `LEFT JOIN` is short for `LEFT OUTER JOIN` in Standard SQL, but it may be that MS-Access only works without the word `OUTER`. Just try. — Thorsten Kettner, Jan 21 '15 at 09:55

score 0 · Answer 4 · answered Jan 21 '15 at 09:31

Using coalesce or similar is the correct solution.

However in your case it's possible to avoid it, the idea is to fetch the dates in a seperate subquery, and then get the sums by left joining to that.

SELECT
Date.EffectiveDate as EffectiveDate,
t1.Total as t1,
t2.Total as t2
FROM
(select distinct EffectiveDate from CProduct
 union
 select distinct EffectiveDate from DProduct
) as Date
left join
(select EffectiveDate,SUM(CFund)
from CProduct
group by EffectiveDate) as t1(EffectiveDate,Total)
on Date.EffectiveDate=t1.EffectiveDate
left join
(select EffectiveDate,SUM(DFund)
from DProduct
group by EffectiveDate) as t2(EffectiveDate,Total)
    on
        Date.EffectiveDate = t2.EffectiveDate

Can you please provide me equivalent working vba query as above query even after replacing COALESCE by Nz, it is giving Syntax Error in From Clause in vba. dnt know why.But working fine in sql. — techV, Jan 21 '15 at 09:38
@vivek This query does not use COALESCE. However see my comment at Damien's answer. — Taemyr, Jan 21 '15 at 09:41

score 0 · Answer 5 · answered Jan 21 '15 at 10:12

0

SELECT E121.EffectiveDate,suM(E12.Cfund) AS CFUND,AVG(e121.Dfund) AS DFUND FROM E121 LEFT JOIN E12 ON E121.EffectiveDate=E12.EffectiveDate GROUP BY E121.EffectiveDate

answered Jan 21 '15 at 10:12

user4477724

1

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

5 Answers5