tabaluga tabaluga - 29 days ago 4
MySQL Question

How to show matches for dates that match date range

I have the following situation:

+------------+-----------+------------+----------------+
| date | event | start_date | duration(days) |
+------------+-----------+------------+----------------+
| 2010-01-01 | concert | 2010-01-01 | 2 |
+------------+-----------+------------+----------------+
| 2010-01-02 | NULL | NULL | NULL |
+------------+-----------+------------+----------------+
| 2010-01-03 | workshops | 2010-01-03 | 3 |
+------------+-----------+------------+----------------+
| 2010-01-04 | NULL | NULL | NULL |
+------------+-----------+------------+----------------+
| 2010-01-05 | NULL | NULL | NULL |
+------------+-----------+------------+----------------+
| 2010-01-06 | NULL | NULL | NULL |
+------------+-----------+------------+----------------+
| 2010-01-07 | NULL | NULL | NULL |
+------------+-----------+------------+----------------+
| 2010-01-08 | festival | 2010-01-08 | 5 |
+------------+-----------+------------+----------------+
| 2010-01-09 | NULL | NULL | NULL |
+------------+-----------+------------+----------------+


Now, I need to display the above information in the way that for every date row when a particular event is happening its name is shown in an event column, not only on the first day when it starts.

There are three tables, one with DATES only, one with EVENTS and one with the START_DATES and DURATION of each event.

So far I have been only able to produce a table like above but I have no idea how to replace NULL values with event name for each day of the duration of the event.

SELECT d AS date, event, start_date, duration
FROM dates
LEFT JOIN info ON info.start_date=d.date AND city='LA'
LEFT JOIN events ON events.event=info.event
WHERE d BETWEEN '2010-01-01' AND '2010-01-09'


Any ideas?

Answer

Below - created a subquery that shows all events with their start date and end date, then joined to the full list of dates:

SELECT d.date, t.event, t.start_date, t.end_date
FROM dates d
LEFT JOIN (SELECT e.event, i.start_date, i.start_date + INTERVAL i.duration DAY AS end_date
            FROM events e
            JOIN info i
                ON e.event = i.event
            WHERE i.city = 'LA'
            ) t
    ON d.date BETWEEN t.start_date AND t.end_date
WHERE d.date BETWEEN '2010-01-01' AND '2010-01-09'
ORDER BY d.date, e.event_name;
Comments