Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I think your question is phrased a bit confusingly, because of the ambiguity between the SQL "Tables" (database tables), and one of the objects in your database, which is (I guess) a restaurant table, called "tafel" in Dutch?</p> <p>(Let's go on calling them "tafel", as this removes the ambiguity)</p> <p>To SELECT only the tafels which are <em>unreserved</em>, you can LEFT join all the tafels to the set of reservations which are current at [desired time], and return only the tafels which don't have such a reservation:</p> <pre><code>SELECT t.* FROM Tafels t LEFT JOIN (SELECT tafelnr FROM Tafels_regel tf INNER JOIN Reserveringen res ON tf.reserveringnr=res.reserveringnr WHERE res.Datum=[desired date] AND res.begintijd&lt;[desired time] AND res.eindtijd&gt;[desired time] ) ReservationsAtThisTime ON t.tafelnr=ReservationsAtThisTIme.tafelnr WHERE ReservationsAtThisTime.tafelNR IS NULL </code></pre> <p>This would perhaps still need some tweaking, as:</p> <ol> <li>Reservations spanning days (i.e. staring on one day and ending on another) would not be handled properly. (But maybe you don't need this)</li> <li>A table would be shown as available at say 10/01/2012 12:30, when it's in fact reserved from 10/01/2012 12:45 (perhaps no-one is interested in a table that isn't free for at least X minutes - 1 hour? - AFTER their desired start time?).</li> </ol> <p><strong>EDIT (to answer OP's comment below):</strong></p> <p>Looking at your data, I can see that you have Tafel 1 reserved from 19:00 to 21:00 on 12/01/2013. I can see why you're not getting the expected results (i.e. Tafel 1 should not be shown, as it's not free):</p> <ol> <li>The date you're using in the query is in 2012 rather than 2013. (This originated perhaps in my example, where I used 2012)</li> <li>I designed my example to use a single, point-in-time "desired time". My thoughts were that the "desired time" would be a point (e.g. 19:30) - and so a reservation starting (e.g.) 19:00 and ending 20:00 would make this desired time unavailable. Because the "desired time" you're using is a range (19:00 to 21:00), and it happens to be <em>exactly</em> the same as the range of the reservation, the "&lt;" and ">" in the query are failing to pick up this reservation as conflicting with the "desired time" ("&lt;=" and ">=" instead would fix this).</li> </ol> <p>However, since you said that all reservations are 2 hours, it would be easier to specify a "conflicting reservation" just using the desired Start time, like this: a "conflicting reservation" is one which occupies any time in the 2 hours following the desired Start time:</p> <p>a. res.Datum = [desired date]; and b. res.eindtijd>[desired start time]; and c. res.begintijd&lt;[desired start time + 2 hours]</p> <p>I'm not completely sure how the code would work to do the addition of 2 hours (my speciality is MS-SQL rather than MySQL, and date/time operations can be a bit specific to the version of SQL).</p> <p><strong>EDIT2 (answering OP's re-redited question):</strong></p> <p>To help make things clear, I'll define these terms:</p> <p><em>Existing</em> booking: already there in the database. Has a TafelNr, Date, StartTime (begintijd) and EndTime (eindtijd)</p> <p><em>Proposed</em> booking: the one the user would like to make - naturally it doesn't exist yet in the database, or have a TafelNr. Has a Date, StartTime, EndTime.</p> <p>(You've implemented the query using both a StartTime and EndTime for the Proposed booking. I suggested just using a StartTime, and then using [StartTime+2 hours]. But which of these ways you use doesn't make a difference to the question we're talking about)</p> <p>You're right that tafels 11 and 12 shouldn't be shown, if the query was working properly. Your Proposed booking from 19:00 to 21:00 should run into a conflict with the Existing booking from 20:00 to 22:00. But as you've found, this conflict is not detected as it should be.</p> <p>The reason is that you've got the Start/End Time comparisons slightly wrong. The way you've coded it:</p> <pre><code>res.begintijd &lt;= [Proposed Start time] AND res.eindtijd &gt;= [Proposed End time] </code></pre> <p>would only find an Existing booking to be conflicting if it <strong>spans</strong> the proposed booking: exactly filling the same time, or extending before and/or after the proposed booking.</p> <p>Imagining the two bookings (existing and proposed) like parallel blocks of Lego sliding along adjacent lines is helpful. They can make no contact at all (good); or just make contact (one's End=the other's Start: good, no conflict - just!); or the end of one can extend over the start of the other (conflict); or one can span the other, starting at the same time or before the other, and ending at the same time as the other or later (conflict).</p> <p>This is a trick I've worked out from working with booking systems years ago. The correct comparison (as in my previous edit above) to find a conflict is:</p> <pre><code>ExistingBookingEndTime&gt;ProposedBookingStartTime AND ExistingBOokingStartTime&lt;ProposedBookingEndTime </code></pre> <p>or, using your object names:</p> <pre><code>res.eindtijd&gt;"19:00:00" AND res.begintijd&lt;"21:00:00" </code></pre> <p>This may not make sense at first, but if you think about the Lego blocks it'll make sense.</p> <p><strong>Note</strong> that:</p> <p><strong>1.</strong> I'm using &lt; and >, not &lt;= and >=, because I assume bookings can extend right up to each other and "touch", as long as they don't overlap;</p> <p><strong>2.</strong> Watch out when comparing dates and times - it can give odd results just comparing them as if they were numbers or strings. I don't know how MySQL handles this, but it's something I'm always careful/paranoid about.</p>
    singulars
    1. This table or related slice is empty.
    1. POReservation system - Available tables
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USsebt
    1. USsebt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POReservation system - Available tables
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYes Thank you sebt, this is correct. I'm working with it right now, cant figure it out though. Ill keep you posted..
      singulars
      1. PO
      1. USNiek van der Linden
    2. COWell been playing with this query for a while now The results I got from the database are all tafels, seems like the date and time dont work. `SELECT t.* FROM tafels t LEFT JOIN (SELECT tafelnr FROM tafels_regel tf INNER JOIN reserveringen res ON tf.reserveringsnr=res.reserveringsnr WHERE res.datum="2012/01/12" AND res.begintijd<"19:00:00" AND res.eindtijd>"21:00:00" ) ReservationsAtThisTime ON t.tafelnr=ReservationsAtThisTime.tafelnr WHERE ReservationsAtThisTime.tafelnr IS NULL` I've also made a screenshot [link](http://goo.gl/tqC47)
      singulars
      1. PO
      1. USNiek van der Linden
    3. COPS - please upvote my answer if it's been helpful to you
      singulars
      1. PO
      1. USsebt
 

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