结合两个SQL查询PDO [英] Combining two SQL queries PDO

问题描述

我对以下问题颇为困惑.我有一系列表格:

I'm quite stuck on the following issue. I have a series of tables:

我想做的是获得一个房间的所有信息，假设预订数量不超过该房间的可用房间号.

What I want to do is get all the information on a room, assuming that the amount of bookings don't exceed the room number available for that Room.

所以要获取我的会议室详细信息，我的SQL是这样的:

So to get my Room details my SQL is this:

  SELECT Rooms.RoomID as RoomID,
         RoomName, NumOfRooms, 
         MaxPeopleExistingBeds, 
         MaxExtraBeds, 
         MaxExtraPeople, 
         CostPerExtraPerson, 
         MaximumFreeChildren, 
         IncludeBreakfast, 
         MinRate 
    FROM Rooms, RoomDetails 
   WHERE Rooms.AccommodationID = :aid AND 
         Rooms.RoomID = RoomDetails.RoomID 
GROUP BY RoomName

返回时，我将得到这些房间的详细信息列表，如下所示:

Which upon return gets me a list of details for those rooms as follows:

然后我使用此查询获取预订数量和房间ID:

I then use this query to get the number of bookings, and the ID of the room:

  SELECT Booking.RoomID, 
         count(Booking.RoomID) as Bookings  
    FROM Booking 
   WHERE ArriveDate >= :aDate AND 
         DepartDate <= :dDate AND 
         AccommodationID = :aid 
GROUP BY RoomID

然后我将两者结合起来，并使用此功能将两个阵列反馈到一个阵列中:

I then combine both and feed the two arrays back in one array using this function:

public function get_availability($aid, $aDate, $dDate) {
        $stmt = $this->db->prepare('SELECT Rooms.RoomID as RoomID, RoomName, NumOfRooms, MaxPeopleExistingBeds, MaxExtraBeds, MaxExtraPeople, CostPerExtraPerson, MaximumFreeChildren, IncludeBreakfast, MinRate FROM Rooms, RoomDetails WHERE Rooms.AccommodationID = :aid AND Rooms.RoomID = RoomDetails.RoomID GROUP BY RoomName');
        $stmt->bindValue(':aid', $aid);
        $stmt->execute();
        $rooms = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $stmt2 = $this->db->prepare('SELECT Booking.RoomID, count(Booking.RoomID) as Bookings  FROM Booking WHERE ArriveDate >= :aDate AND DepartDate <= :dDate AND AccommodationID = :aid GROUP BY RoomID');
        $stmt2->bindValue(':aid', $aid);
        $stmt2->bindValue(':aDate', $aDate);
        $stmt2->bindValue(':dDate', $dDate);
        $stmt2->execute();
        $bookings = $stmt2->fetchAll(PDO::FETCH_ASSOC);
        $room = array($rooms, $bookings);

        return (!empty($room)) ? $room : false;
    }

问题是，我真正想做的只是返回NumOfRooms少于预订数的房间详细信息.

The thing is, what I actually want to do is only return the room details where NumOfRooms is less than the number of Bookings.

例如，如果我有$ books，如果它告诉我ID为4的房间，在设定的时间内我有3个预订，而我的NumOfRooms是1.那么我知道那周我没有能力接受任何预订更多预订.但是，如果我有1个预订和一个名额，那么那还是满的.但是，如果我的NumOfRooms为2，并且预订数为1，我知道我有房间.

So for instance where I have $bookings, if it tells me that for room ID 4, I have 3 bookings for a set period, and my NumOfRooms is 1. Then I know that I have no capacity that week to take any more bookings on. If however I have 1 booking and one capacity then that is still full. But if I have NumOfRooms of 2, and bookings amount to 1, I know I have room.

因此，基本上，如果NumOfRooms> BookingCount，那么房间就可用.

So basically if NumOfRooms > BookingCount then the room is available.

如何合并两个查询并简化代码以实现此目的?

How can I amalgamate both queries and simplify my code to make this possible?

IE简而言之，我如何从RoomDetails中选择所有信息，给定预订中的ArriveDate，DepartDate和RoomID，其中NumOfRooms> count(Booking.RoomID)(在这些日期和房间内) id等于Rooms的房间ID).

I.E to put it simply, how do I select all of the info from RoomDetails given an ArriveDate in Booking and a DepartDate and a RoomID, where NumOfRooms > count(Booking.RoomID) (Where it is within those dates and the room id is equal to the room id of Rooms).

推荐答案

您的问题可以通过简单地更新SQL语句本身来解决:

Your problem can be solved by simply updating the SQL statement itself:

SELECT r.RoomID AS RoomID,
       RoomName,
       NumOfRooms,
       MaxPeopleExistingBeds,
       MaxExtraBeds,
       MaxExtraPeople,
       CostPerExtraPerson,
       MaximumFreeChildren,
       IncludeBreakfast,
       MinRate
FROM Rooms r
JOIN RoomDetails rd
    ON r.RoomID = rd.RoomID
JOIN (
    SELECT b.RoomID,
           AccommodationID,
           count(b.RoomID) AS Bookings
    FROM Booking b
    WHERE ArriveDate >= :aDate
      AND DepartDate <= :dDate
    GROUP BY RoomID
) t
    ON t.AccommodationID = r.AccommodationID
WHERE r.AccommodationID = :aid
    AND t.Bookings < NumOfRooms
GROUP BY RoomName

这篇关于结合两个SQL查询PDO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！