CODEWITHSUNDEEP
mysqlnestedbetween
El Mauro
El Mauro

Reputation: 31

MySQL nested Select BETWEEN multiple rows

I have 2 tables:
Table 1 called calendar and have all dates and days
Example:

---------------------
date       | day
---------------------
2013-07-08 | Tuesday
2013-07-09 | Wednesday

and so..

Table 2 called "events" and have event start dates and end dates
Example:

-------------------------------------------
title     | date        | end_date
-------------------------------------------
eventweek | 2013-07-08  | 2013-07-14

etc.

Now I need to select the days between those dates which would be:

SELECT days
FROM   calendar
WHERE  date BETWEEN
                     (
                     SELECT date
                     FROM   events
                     WHERE  title LIKE %eventweek%)
AND
       (
              SELECT end_date
              FROM   events
              WHERE  title LIKE %eventweek%).

Now the problem is when there is multiple "eventweek" titled events like this:

-------------------------------------------
title     | date        | end_date
-------------------------------------------
eventweek | 2013-07-08  | 2013-07-14
eventweek | 2014-07-01  | 2014-07-13

of course I will get "subquery returns more than 1 row".

Expected results in this example are the days between 2013-07-08 and 2013-07-14 plus the days 2014-07-01 and 2014-07-13 and every future "eventweek" tagged entry.

Btw this gonna be a table view so maybe can't be solved with a loop. Any ideas?

Thanks a lot for any suggestion, I will try them all to see the simpler and faster one.

Upvotes: 1

Views: 293

Answers (1)

Victorqedu
Victorqedu

Reputation: 494

I don't think between is a good start, this should be better:

SELECT c.day
FROM events e
join calendar c on (c.date>=e.date and c.date<=e.end_date)
WHERE e.title = 'eventweek'

Upvotes: 2

Related Questions