Combining two SQL queries PDO

Question

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.

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:

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;
    }

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

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.

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

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

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).

Answer 1

You can select out all of the booking counts per room for the desired date range as a subquery, and then LEFT JOIN that subquery against the list of your rooms filtered by your desired AccommodationID and the desired NumOfRooms > BookingCount criteria. The key here is in the join type used for this subquery, as an inner join would limit your results to only rooms that actually had bookings.

    SELECT Rooms.RoomID as RoomID,
           RoomName, NumOfRooms, 
           MaxPeopleExistingBeds, 
           MaxExtraBeds, 
           MaxExtraPeople, 
           CostPerExtraPerson, 
           MaximumFreeChildren, 
           IncludeBreakfast, 
           MinRate,
           BookingCount
      FROM Rooms
INNER JOIN RoomDetails on Rooms.RoomID = RoomDetails.RoomID
 LEFT JOIN (
               SELECT Booking.RoomID,
                      count(Booking.RoomID) as BookingCount
                 FROM Booking 
                WHERE ArriveDate >= :aDate AND 
                      DepartDate <= :dDate
             GROUP BY Booking.RoomID
           ) RoomBookings ON Rooms.RoomID = RoomBookings.RoomID
     WHERE Rooms.AccommodationID = :aid
       AND NumOfRooms > BookingCount
  GROUP BY RoomName

Answer 2

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