MySQL - 查询出了什么问题？

Question

我正在尝试查询数据库以查找以下内容。

如果客户在日期 A 和 B 之间搜索某个城市的酒店，则查找并返回在这两个日期之间有空房的酒店。

每种房型都会有多个房间（即 A 型有 5 个房间，B 型有 10 个房间等），我们必须查询数据库以仅查找其中至少有一个空闲房间的酒店至少一种类型。

这是我的表结构：

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

这是我的查询：

$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'";

每个酒店的每种房型中已预订的房间数量将存储在预订表中。

问题是查询根本不返回任何结果。即使我自己手动计算也应该如此。

我尝试单独运行子查询，但没有得到任何结果。我在尝试调试昨天的这个查询时掉了很多头发。这有什么问题吗？或者有更好的方法来做我上面提到的事情吗？

编辑：编辑代码以删除错误。感谢马克·拜尔斯。

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

子查询应该返回

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

您的问题在于：

$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'"

您正在执行减法 total_rooms - (tot, id)，其中第一个操作数是标量值，第二个操作数是包含两列的表。删除结果集中的一列并确保只返回一行。

您还应该使用 JOIN 关键字进行连接，而不是用逗号分隔表。这样您就不会忘记添加连接条件。

您可能想要以下内容：

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'