sql - Mysql three table join group by each day

Question

I have three tables, and each table has the same column information. So I want to join these three tables by their date. But If one of these tables doesn't have date info, then make it zero. I have tried left join three tables, but it doesn't work. P

This table is for a diesel sell table. diesel_data

id | sell_quantity | earn | investment | profit | date
 1 |      30       | 300  |    150     |   150   | 15/01/2021
 2 |      40       | 400  |    200     |   200   | 15/01/2021
 3 |      50       | 500  |    350     |   150   | 18/01/2021
 2 |      40       | 400  |    340     |    60   | 19/01/2021 

This table is for an octane sell table. octane_data

id | sell_quantity | earn | investment | profit  | date
 1 |      20       | 200  |    150     |    50   | 15/01/2021
 2 |      35       | 340  |    300     |    40   | 18/01/2021
 3 |      54       | 530  |    500     |    30   | 18/01/2021

This table is for a Mobil sell table. mobil_data

id | sell_quantity | earn | investment | profit | date
 1 |      20       | 240  |    120     |    120 | 15/01/2021
 2 |      31       | 310  |    300     |    10  | 16/01/2021
 3 |      54       | 540  |    500     |    40  | 18/01/2021

My desire table is that each date has info; if not, fill it with 0 and select the query group by date

My desire table

     date|sum(sell_quan(dies)|sum(prof)|sum(sell_qu)oct|sum(prof)oct|sum(sel_qu)mob|sum(prof)mob|
 15/01/2021   |      70      |  350    |     20        |    50      |        20    |  120
 16/01/2021   |       0      |   0     |      0        |     0      |        31    |   10
 17/01/2021   |       0      |   0     |      0        |     0      |         0    |    0
 18/01/2021   |      50      |  150    |     89        |    70      |        54    |   40
 19/01/2021   |      40      |   60    |      0        |     0      |         0    |    0
 20/01/2021   |       0      |    0    |      0        |     0      |         0    |    0

Answer 1

This sounds like union all . . . along with a way to get the dates. You can generate the dates using a recursive CTE (or other method):

with recursive dates as (
      select  '2021-01-15' as date
      union all
      select date + interval 1 day
      from dates
      where date < '2021-01-20'
     )
select dates.date,
       coalesce(sum(sell_quantity), 0),
       coalesce(sum(earn), 0),
       coalesce(sum(investment), 0),
       coalesce(sum(profit), 0)
from dates d left join
     ((select id, sell_quantity, earn, investment, profit, date
       from diesel_data
      ) union all
      (select id, sell_quantity, earn, investment, profit, date
       from octane_data
      ) union all
      (select id, sell_quantity, earn, investment, profit, date
       from mobil_data
      )
     ) d
     on d.date = dates.date
group by dates.date;