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

reservations表中会存储每家酒店每种房型的已预定房间数。

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

我尝试单独运行子查询，但没有得到任何结果。我已经失去了相当多的头发，试图从昨天开始调试这个查询。这有什么问题吗？还是有更好的方法来完成我上面提到的事情？

编辑:编辑代码以消除错误。感谢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

子查询应该返回

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'