MySQL - What's wrong with the query?

Question

I am trying to query a database to find the following.

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.

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.

This is my table structure:

**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

And this is my query:

$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 >= '$check_in_date' AND  $dateFormat_check_out <= '$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) > '0'";

The number of rooms already reserved in each room type in each hotel will be stored in the reservations table.

The thing is the query doesn't return any result at all. Even though it should if I calculate it myself manually.

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?

Edit: Code edited to remove a bug. Thanks to Mark Byers.

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

The sub-query should return

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

Answer 1

Your problem is here:

$rooms.total_rooms - (SELECT SUM($reservations.no_of_rooms) as tot,
                                               $rooms.room_id as id
                                               FROM $reservations,$rooms
                                               WHERE $dateCheck
                                               GROUP BY $reservations.room_id) > '0'"

You are doing a subtraction total_rooms - (tot, id) where the first operand is a scalar value and the second is a table with two columns. Remove one of the columns in the result set and make sure you only return only one row.

You also should use the JOIN keyword to make joins instead of separating the tables with commas. That way you won't forget to add the join condition.

You probably want something along these lines:

SELECT column1, column2, etc...
FROM   $hotels
JOIN   $rooms
ON     $hotels.hotel_id = $rooms.hotel_id
JOIN   (
    SELECT SUM($reservations.no_of_rooms) as tot,
           $rooms.room_id as id
           FROM $reservations
           JOIN $rooms
           ON ??? /* Aren't you missing something here? */
           WHERE $dateCheck
           GROUP BY $reservations.room_id
) AS T1
ON     T1.id = room_id
WHERE  $hotels.hotel_city = '$city_search'
AND    $hotels.hotel_deleted = '0'
AND    $rooms.room_deleted = '0'
AND    $rooms.total_rooms - T1.tot > '0'