Hi everybody....

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 db to find only those hotels in which there is atleast one room free in atleast 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,
                                                   $rooms.room_id as id
                                                   FROM $reservations,$rooms
                                                   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 query returns the error :

Operand should contain 1 column(s) 

I tried running the sub-query alone but 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...?

Thanks for your time...