title: MySQL - What's wrong with the query?
body: <p>I am trying to query a database to find the following.</p>
  
  <p>If a customer searches for a hotel in a city between dates A and B, find and return the hotels in which rooms are free between the two dates. </p>
  
  <p>There will be more than one room in each room type (i.e. 5 Rooms in type A, 10 rooms in Type B, etc.) and we have to query the database to find only those hotels in which there is at least one room free in at least one type.</p>
  
  <p>This is my table structure:</p>
  
  <pre><code>**Structure for table 'reservations'**
      reservation_id
      hotel_id
      room_id
      customer_id
      payment_id
      no_of_rooms
      check_in_date
      check_out_date
      reservation_date
  
      **Structure for table 'hotels'**
      hotel_id
      hotel_name
      hotel_description
      hotel_address
      hotel_location
      hotel_country
      hotel_city
      hotel_type
      hotel_stars
      hotel_image
      hotel_deleted
  
  
      **Structure for table 'rooms'**
      room_id
      hotel_id
      room_name
      max_persons
      total_rooms
      room_price
      room_image
      agent_commision
      room_facilities
      service_tax
      vat
      city_tax
      room_description
      room_deleted
  </code></pre>
  
  <p>And this is my query:</p>
  
  <pre><code>$city_search = '15';
  $check_in_date = '29-03-2010';
  $check_out_date = '31-03-2010';
  
  $dateFormat_check_in = "DATE_FORMAT('$reservations.check_in_date','%d-%m-%Y')";
  $dateFormat_check_out = "DATE_FORMAT('$reservations.check_out_date','%d-%m-%Y')";
  
  $dateCheck = "$dateFormat_check_in &gt;= '$check_in_date' AND  $dateFormat_check_out &lt;= '$check_out_date'";
  
   $query = "SELECT $rooms.room_id,
                    $rooms.room_name,
                    $rooms.max_persons,
                    $rooms.room_price,
                    $hotels.hotel_id,
                    $hotels.hotel_name,
                    $hotels.hotel_stars,
                    $hotels.hotel_type
             FROM   $hotels,$rooms,$reservations
             WHERE  $hotels.hotel_city = '$city_search'
             AND    $hotels.hotel_id = $rooms.hotel_id
             AND    $hotels.hotel_deleted = '0'
             AND    $rooms.room_deleted = '0'
             AND    $rooms.total_rooms - (SELECT SUM($reservations.no_of_rooms) as tot
                                                     FROM $reservations
                                                     WHERE $dateCheck
                                                     GROUP BY $reservations.room_id) &gt; '0'";
  </code></pre>
  
  <p>The number of rooms already reserved in each room type in each hotel will be stored in the reservations table.</p>
  
  <p>The thing is the query doesn't return any result at all. Even though it should if I calculate it myself manually.</p>
  
  <p>I tried running the sub-query alone and I don't get any result. And I have lost quite some amount of hair trying to de-bug this query from yesterday. What's wrong with this? Or is there a better way to do what I mentioned above?</p>
  
  <p>Edit: Code edited to remove a bug. Thanks to <a href="https://stackoverflow.com/users/61974/mark-byers">Mark Byers</a>.</p>
  
  <pre><code>Sample Data in reservation table
  
  1   1   1   2   1   3   2010-03-29  2010-03-31  2010-03-17
  2   1   2   3   3   8   2010-03-29  2010-03-31  2010-03-18
  5   1   1   5   5   4   2010-03-29  2010-03-31  2010-03-12
  </code></pre>
  
  <p>The sub-query should return </p>
  
  <pre><code>Room ID : 1    Rooms Booked : 7
  Room ID : 2    Rooms Booked : 8
  
  But it does not return any value at all.... If i remove the dateCheck condition it returns
  Room ID : 2    Rooms Booked : 8
  </code></pre>