mysqldatejoindate-range

SQl join on date range

I have a table

ID, Start Date, End Date

I have a second table which has one column, dates from 2000 to 2050. It is a key to join start and end date on Date_ID

I would like to take a table starting like this

ID, Start Date, End Date
123, 1/1/2017,  1/5/2017

Then join it onto the Date Table (second table) to output a table like this

ID, Start Date, End Date, Day Date
123, 1/1/2017,  1/5/2017, 1/1/2017
123, 1/1/2017,  1/5/2017, 1/2/2017
123, 1/1/2017,  1/5/2017, 1/3/2017
123, 1/1/2017,  1/5/2017, 1/4/2017
123, 1/1/2017,  1/5/2017, 1/5/2017

I thought of joining on a date range but that does not work. Any help/suggestions would be much appreciated! I am using MYSQL.

SELECT 
    name,
    date(start_date),
    date(end_date),
    d.id as Day_Date
FROM
    f_table1 a
Left Join
    d_table2 d on d.id = d.id between date(a.start_date) and date(a.end_date)

The above code of course does not run, but this is my best stab thus far.

Solution 
  • SELECT 
    name,
    date(start_date),
    date(end_date),
    d.id as Day_Date
FROM       f_table1 a
Left Join  d_table2 d 
    on a.id = d.id 
   and d.`Day Date` between date(a.start_date) and date(a.end_date)