title: Show available rooms between to dates? SQL
body: <p>I hope somebody can give me some little help here...</p>
  
  <p>I'm programming a little booking program for a hotel, where I should be able to see a list of available rooms between two given dates. I'm experiencing an issue as in that if i search with an arrival date that is after the arrival date of the booking, the room will still show up as available...</p>
  
  <p>My call to the database looks like this:</p>
  
  <pre><code>String SQLString = "select roomnumber, roomtypeid from rooms "
          + "where roomnumber not in "
          + "(select roomnumber from booking "
          + "where arrival between to_date(?, 'yyyy-mm-dd') "
          + "and to_date(?, 'yyyy-mm-dd')) and roomtypeid = ? order by roomnumber";
  </code></pre>
  
  <p>I have tried some different combinations of sql strings, but nothing helps. Can anybody give me a good hint?</p>
  
  <pre><code>CREATE TABLE BOOKING(
  ARRIVAL             DATE ,
  DEPARTURE           DATE,
  RESERVATIONSNUMBER  INTEGER NOT NULL PRIMARY KEY,
  ROOMNUMBER          INTEGER,
  PAID                INTEGER,
  CUSTOMERID INTEGER,
  NUMBEROFGUESTS INTEGER,
  CONSTRAINT BOFK FOREIGN KEY(ROOMNUMBER) REFERENCES ROOMS(ROOMNUMBER),
  CONSTRAINT CUIDFK FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMER(CUSTOMERID));
  </code></pre>