LEFT JOIN 3rd Table to the original Table rather than the resultant table

Question

I have two tables such as

1. Income(point, date, inc)
   
2. Outcome(point, date, out)
   

where point and date are the primary key.
I need to find out the total income inc and outcome out on any given date. My answer

WITH all_date AS
(SELECT point, date FROM income
UNION
SELECT point, date FROM outcome)
SELECT a.point, a.date, SUM(inc), SUM(out)
FROM all_date a LEFT JOIN income i ON i.point=a.point AND i.date=d.date
                LEFT JOIN outcome o ON o.point=a.point AND o.date=a.date
GROUP BY a.point, a.date

But I am getting wrong result. Because the all_date LEFT JOIN income while trying to do LEFT JOIN with outcome duplicates certain rows from income, hence messing with the final SUM.

Basically I want to LEFT JOIN the third table outcome with all_date not with all_date LEFT JOIN income. Refer to Qus No 30. of http://www.sql-ex.ru/learn_exercises.php for further clarification

Answer 1

Why not do that join first then?

or even better, just do a full outer join:

Select coalesce(o.point, i.point) point,
   coalesce(o.Date, i.date) date
From income i full join outcome o
  on o.point=i.point 
     and o.date=i.date
group by coalesce(o.point, i.point),
     coalesce(o.Date, i.date)

and, since you're not actually using any aggregation functions, a distinct would work as well:

Select distinct
   coalesce(o.point, i.point) point,
   coalesce(o.Date, i.date) date
From income i full join outcome o
  on o.point=i.point 
     and o.date=i.date

My apologies, I'm not a MySQL guy and did not realize that MySQL does not have Full Join syntax. but there is a workaround. look at the following link:

Full Join in MYSQL

basically you need a Union, (close to what you were doing, actually)

select point, date 
from income i
 left join outcome o 
    on o.point=i.point 
      and o.date=i.date  
  UNION                 -- <-- leave out the ALL to eliminate duplicates
select point, date 
from outcome o
 right join income i 
    on i.point=o.point 
      and i.date=o.date  

Answer 2

I do not know what is the purpose of using table expression here, you can get the result by using one join here:

SELECT i.point, i.date, SUM(inc), SUM(out)
FROM  income i 
LEFT JOIN outcome o   
ON o.point=i.point AND o.date=i.date
GROUP BY i.point, i.date

Answer 3

MS-SQL sp for your problem:

CREATE PROCEDURE dototals
@date DATETIME
AS
BEGIN

SELECT point,SUM(ISNULL(inc,0)) as inc into #tmp_i FROM Income WHERE date =@date  GROUP BY point

SELECT point,SUM(ISNULL(out,0)) as out into #tmp_d FROM Outcome WHERE date =@date  GROUP BY point

SELECT DISTINCT point into #tmp_p FROM 
(SELECT point FROM #tmp_i
UNION ALL
SELECT point FROM #tmp_o)

SELECT @date as date, t1.point as point, t2.out as outcome,t3.inc as income 
FROM #tmp_p as t1
LEFT JOIN #tmp_o as t2 on t1.point=t2.point
LEFT JOIN #tmp_i as t3 on t1.point=t3.point
END

Answer 4

Here is my final solution in MSSQL. But I guess using COALESCE with FULL JOIN works the best.

    WITH all_date AS
    (SELECT point, date FROM income
    UNION
    SELECT point, date FROM outcome)

    SELECT a.point, a.date,
    (SELECT SUM(o.out) FROM outcome o WHERE o.point=a.point AND o.date=a.date) AS outcome,
    (SELECT SUM(i.inc) FROM income i WHERE i.point=a.point AND i.date=a.date) AS income

    FROM all_date a