MySQL select rows with dates range for each days of the range

Question

I have a table containing events with ranges like this :

id | title | start      | end
1  | Lorem | 2019-11-02 | 2019-11-03
2  | Ipsum | 2019-11-02 | 2019-11-02
3  | Dolor | 2019-11-08 | 2019-11-10
4  | Amet  | 2019-11-02 | 2019-11-04

I want to select all rows but joining the dates from the range, so I can have a X rows for each event for each day from its range. The results should be from my example table :

date        | id | title | start      | end
2019-11-02  | 1  | Lorem | 2019-11-02 | 2019-11-03
2019-11-02  | 2  | Ipsum | 2019-11-02 | 2019-11-02
2019-11-02  | 4  | Amet  | 2019-11-02 | 2019-11-04
2019-11-03  | 1  | Lorem | 2019-11-02 | 2019-11-03
2019-11-03  | 4  | Amet  | 2019-11-02 | 2019-11-04
2019-11-04  | 4  | Amet  | 2019-11-02 | 2019-11-04
2019-11-08  | 3  | Dolor | 2019-11-08 | 2019-11-10
2019-11-09  | 3  | Dolor | 2019-11-08 | 2019-11-10
2019-11-10  | 3  | Dolor | 2019-11-08 | 2019-11-10

I'm really stuck and don't know if it's event possible.... Thanks for your help ! I'm on MySQL 5.7

Answer 1

If you are running MySQ 8.0, this is a straight-forward recursive query:

with recursive cte as (
    select start as date, id, title, start, end from mytable
    union all
    select date + interval 1 day, id, title, start, end from cte where date < end
)
select * from cte
order by date, id

Demo on DB Fiddle:

date       | id | title | start      | end       
:--------- | -: | :---- | :--------- | :---------
2019-11-02 |  1 | Lorem | 2019-11-02 | 2019-11-03
2019-11-02 |  2 | Ipsum | 2019-11-02 | 2019-11-02
2019-11-02 |  4 | Amet  | 2019-11-02 | 2019-11-04
2019-11-03 |  1 | Lorem | 2019-11-02 | 2019-11-03
2019-11-03 |  4 | Amet  | 2019-11-02 | 2019-11-04
2019-11-04 |  4 | Amet  | 2019-11-02 | 2019-11-04
2019-11-05 |  3 | Dolor | 2019-11-05 | 2019-11-08
2019-11-06 |  3 | Dolor | 2019-11-05 | 2019-11-08
2019-11-07 |  3 | Dolor | 2019-11-05 | 2019-11-08
2019-11-08 |  3 | Dolor | 2019-11-05 | 2019-11-08

In earlier versions, typical solutions include a table of numbers. Here is one solution that will handle up to 4 days span (you can extend the subquery for more):

select
    t.start + interval x.n day date,
    t.*
from 
mytable t
inner join (
    select 0 n union all select 1 union all select 2 union all select 3 union all select 4
) x on t.start + interval x.n day <= t.end
order by date, id

Demo on DB Fiddlde