title: MySQL - Finding time overlaps
body: <p>I have 2 tables in the database with the following attributes:</p>
  
  <pre><code>Booking
  =======
  booking_id
  booking_start
  booking_end
  
  resource_booked
  ===============
  booking_id
  resource_id
  </code></pre>
  
  <p>The second table is an associative entity between "Booking" and "Resource" (i.e., 1 booking can contain many resources). Attributes booking_start and booking_end are timestamps with date and time in it.</p>
  
  <p>May I know how I might be able to find out for each resource_id (resource_booked) if the date/time overlaps or clashes with other bookings of similar resource_id?</p>
  
  <p>I was doodling the answer on paper, pictorially, to see if it might help me visualize how I could solve this and I got this:</p>
  
  <ol>
  <li>Joining the 2 tables (Booking, Booked_resource) into one table with the 4 attributes needed.</li>
  <li>Follow the answer suggested here : <a href="https://stackoverflow.com/questions/689458/find-overlapping-date-time-rows-within-one-table">Find overlapping (date/time) rows within one table</a></li>
  </ol>
  
  <p>I did step 1 but step 2 is leaving me baffled!</p>
  
  <p>I would really appreciate any help on this! Thanks! </p>
  
  <p>EDIT:
  I was reading Mr Renshaw's answer and tried doing one on my own to see if I understood and I got the concept:</p>
  
  <pre><code>SELECT 
    a.* 
  FROM 
    (SELECT 
      b.creation_date,
      b.booking_id,
      r_b.resource_id,
      b.booking_start,
      b.booking_end 
    FROM Booking b 
    INNER JOIN resource_booked r_b ON b.booking_id = r_b.booking_id) as a,
    (SELECT
      b.booking_id,
      r_b.resource_id,
      b.booking_start,
      b.booking_end 
    FROM Booking b INNER JOIN resource_booked r_b ON b.booking_id = r_b.booking_id) as
  WHERE
    a.resource_id = b.resource_id 
  AND 
    a.booking_id &lt;&gt; b.booking_id 
  AND 
    a.booking_start BETWEEN b.booking_start AND b.booking_end 
  AND
    a.creation_date &gt;= b.creation_date
  </code></pre>
  
  <p>I <strong>think</strong> I was trying to create 2 identical tables and join them up with resource_id, find records with similar resource id but different booking_id and see if the booking_start datetime of one (booking_id) is between the booking_start and booking_end of another (booking_id).</p>
  
  <p>It's really messy and I wasn't even sure if my query was asking what I had in mind but by some miracle, I got the same answer as Mr Renshaw!</p>