MySQL Choose between an unknown number of date intervals

Question

I currently have two tables, one called games, the other rounds. Here is a structure to help:

rounds

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| schedule_id | int(11)  | NO   | MUL | NULL    |                |
| open_date   | datetime | NO   |     | NULL    |                |
| close_date  | datetime | NO   |     | NULL    |                |
| start_date  | datetime | NO   |     | NULL    |                |
| end_date    | datetime | NO   |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

games

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| schedule_id | int(11)  | NO   | MUL | NULL    |                |
| team_a      | int(11)  | NO   | MUL | NULL    |                |
| team_b      | int(11)  | NO   | MUL | NULL    |                |
| winner      | int(11)  | YES  | MUL | NULL    |                |
| date        | datetime | NO   |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

Now the round table is a bunch of rounds with the specified date ranges, and the games are set in a round date range (but not always). In any case, I want to select all the games between several rounds. Therefore, if I have three round intervals,

round 1 - nov 13 to nov 15
round 2 - nov 20 to nov 25
round 3 - dec 1 to nov 10

how to select only those games that are between these date periods without executing multiple SQL statements. Essentially, I want to end up with something like this:

SELECT * 
FROM games 
WHERE date BETWEEN round1.start_date AND round1.end_date OR
      date BETWEEN round2.start_date AND round2.end_date OR
      date BETWEEN round3.start_date AND round3.end_date

, . max min, , , . , , PHP. , .

, !

:

:

SELECT * 
FROM games INNER JOIN 
     rounds ON games.`date` BETWEEN rounds.start_date AND rounds.end_date

- ? , , , , .

Answer 1

...

select `games`.* from games join rounds on (`games`.`date` < `rounds`.`end_date` AND `games`.`date` > `rounds`.`start_date`);