Recursive CTE in MySQL for missing dates

Question

I have two tables DT and averages:

CREATE TABLE averages (
  `workdate` DATE NULL,
  `emp` VARCHAR(45) NOT NULL,
  `Score1` INT NULL,
  `Score2` INT NULL);

INSERT INTO averages VALUES
('2021-06-01','Sal','12','1'),
('2021-06-02','Sal','11','1'),
('2021-05-28','Rick','10','1'),
('2021-06-01','Rick','12','1'),
('2021-06-02','Liz','11','1'),
('2021-05-28','Liz','10','1');

CREATE TABLE datestemp AS
SELECT Min(workdate) AS date, emp
FROM averages
GROUP BY emp;

CREATE TABLE DT AS
with recursive cte as (
      select emp, date
      from datestemp
      union all
      select emp, date + interval 1 day
      from cte
      where date <= curdate()
     )
select *
from cte
order by emp, date;

I want the averages table to have the dates like that in the DT table i.e. I want the averages.workdate column to have all dates for an employee(emp) from the minimum date to today's date. Then the columns Score1 and Score2 can have 0's wherever the extra dates are added by the recursive CTE function. I want averges table to look something like this:

workdate	emp	score1	score2
2021-06-01	Sal	12	1
2021-06-02	Sal	11	1
2021-06-03	Sal	0	0
2021-06-04	Sal	0	0
2021-05-28	Rick	10	1
2021-05-29	Rick	0	0
2021-05-30	Rick	0	0
2021-05-31	Rick	0	0
2021-06-01	Rick	12	1
2021-06-02	Rick	0	0
2021-06-03	Rick	0	0
2021-06-04	Rick	0	0

....and so on.

I tried Joining both the tables but not able to get the result I want. Probably there is a way to skip creating the DT table and use the minimum of dates from the average table and then use recursive CTE on it? I am not sure if or how that will work, new to recursive CTE function. Any ideas on how to go about this?

The db<>fiddle for the same is here: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0010c4d5e3a1c2eac9c065b31344f128

Thank you in advance!

Answer 1

WITH RECURSIVE
cte AS ( SELECT MIN(dt) dt
         FROM table
         UNION ALL
         SELECT dt + INTERVAL 1 DAY FROM cte WHERE dt < CURRENT_DATE )
SELECT cte.dt, table.*
FROM cte
JOIN table ON table.dt <= cte.dt;