Databases
StackOverflow2013
Postsdbo
POMySQL Select between an unknown number of date intervals
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL Select between an unknown number of date intervals
    primarykey
    data
    text
    <p>I currently have two tables, one is called games, the other called rounds. Here is the structure to help out:</p> <p>rounds</p> <pre><code>+-------------+----------+------+-----+---------+----------------+ | 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 | | +-------------+----------+------+-----+---------+----------------+ </code></pre> <p>games</p> <pre><code>+-------------+----------+------+-----+---------+----------------+ | 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 | | +-------------+----------+------+-----+---------+----------------+ </code></pre> <p>Now the rounds table is a bunch of rounds with specified date ranges, and games are set within the round date range (but not always). Anyways, What I want to do is select all games between multiple round intervals. So if I have three round intervals,</p> <pre><code>round 1 - nov 13 to nov 15 round 2 - nov 20 to nov 25 round 3 - dec 1 to nov 10 </code></pre> <p>how do I only select the games that are between those date periods without doing multiple SQL statements. Essentially I want something like this in the end:</p> <pre><code>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 </code></pre> <p>but I don't know how many rounds I will have. I also cannot use a max a min because there might be rounds between those intervals that I do not want. Right now my only solution is that I do one select for all the rounds, then do a select for each interval through PHP. I am hoping that there might be a better, more efficient way to accomplish this.</p> <p>Hopefully this made sense and thanks!</p> <h2>Update:</h2> <p>After posting this question, I had an idea of a query like this:</p> <pre><code>SELECT * FROM games INNER JOIN rounds ON games.`date` BETWEEN rounds.start_date AND rounds.end_date </code></pre> <p>does something like that make sense? It seems to work, but I've never joined like that, so I don't know what type of effects I might encounter.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCœur
    1. USJohnathanKong
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL Select between an unknown number of date intervals
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL Select between an unknown number of date intervals
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL Select between an unknown number of date intervals
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI don't see any keys in your schema that relate to `rounds`. If they are simply a logical grouping, I don't believe you can do this with SQL alone.
      singulars
      1. POMySQL Select between an unknown number of date intervals
      1. USJason McCreary
    2. COIt is just a logical grouping.
      singulars
      1. POMySQL Select between an unknown number of date intervals
      1. USJohnathanKong
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload