VeeK VeeK - 4 months ago 16
SQL Question

MySQL - Find hotels that are available on certain dates

I've read many similar questions but haven't been able to make it work for myself. I'm creating a hotel booking system and I wish to list hotels that have atleast one room available on requested booking dates.

Here's my table structure
Table Structure

And here's the query that I have so far

SELECT
ac.id,
ac.name,
ac.link,
ac.type,
ac.country,
co.name AS countryName,
ac.state,
st.statename,
ac.cityid AS cityId,
ci.name AS cityName,
ac.addr,
ac.featuredimage,
ac.amenities,
ac.starrating,
ac.bookings_received,
ro.roomprice,
SUM(ro.numberofrooms) AS totalRooms,
roomsBooked = (
SELECT
*,
SUM(numberofroomsbooked) AS roomsBooked
FROM
bookings
WHERE
reservationto >= '2016-07-07' AND reservationfrom <= '2016-07-09'
AND hotelid = ro.hotel
)
FROM
accomodations ac
JOIN countries co ON ac.country = co.id
JOIN states st ON ac.state = st.id
JOIN city ci ON ci.id = ac.cityid
JOIN room ro ON ac.id = ro.hotel
WHERE
ac.active = 1 AND ac.delete = 0
GROUP BY
ac.id


I'm new to MySQL and this is really getting out of hand. The idea is to calculate total number of rooms for a hotel and subtract total number of rooms booked between a given date range. Can I get some help on that?

The Sub query

SELECT
*,
SUM(numberofroomsbooked) AS roomsBooked
FROM
bookings
WHERE
reservationto >= '2016-07-07' AND reservationfrom <= '2016-07-09'
AND hotelid = ro.hotel


works perfectly on it's own but not in this one.

Answer

Edit: Built upon the previous query

SELECT
  ac.id,
  ac.name,
  ac.link,
  ac.type,
  ac.country,
  co.name AS countryName,
  ac.state,
  st.statename,
  ac.cityid AS cityId,
  ci.name AS cityName,
  ac.addr,
  ac.featuredimage,
  ac.amenities,
  ac.starrating,
  ac.bookings_received,
  ro.roomprice,
  ro.totalRooms
FROM
  accomodations ac
JOIN countries co ON ac.country = co.id
JOIN states st ON ac.state = st.id
JOIN city ci ON ci.id = ac.cityid
JOIN (
  SELECT
    hotel,
    roomprice,
    SUM(numberofrooms) AS totalRooms
  FROM
    room
  GROUP BY
    hotel
) ro ON ac.id = ro.hotel
JOIN (
  SELECT
    hotelid,
    SUM(numberofroomsbooked) ASroomsBooked
  FROM
    bookings
  WHERE
    reservationto >= '2016-07-07' AND reservationfrom <= '2016-07-09'
  GROUP BY
    hotelid
) bo ON ac.id = bo.hotelid
WHERE
  ac.active = 1 AND ac.delete = 0
GROUP BY
  ac.id

Updated query removed inner select and added it as inner join

SELECT
  ac.id,
  ac.name,
  ac.link,
  ac.type,
  ac.country,
  co.name AS countryName,
  ac.state,
  st.statename,
  ac.cityid AS cityId,
  ci.name AS cityName,
  ac.addr,
  ac.featuredimage,
  ac.amenities,
  ac.starrating,
  ac.bookings_received,
  ro.roomprice,
  SUM(ro.numberofrooms) AS totalRooms,
  SUM(tm.roomsBooked ) as roomsBooked,
FROM
  accomodations ac
JOIN countries co ON ac.country = co.id
JOIN states st ON ac.state = st.id
JOIN city ci ON ci.id = ac.cityid
JOIN room ro ON ac.id = ro.hotel
(
  SELECT
    hotelid,
    SUM(numberofroomsbooked) AS roomsBooked
  FROM
    bookings
  WHERE
    reservationto >= '2016-07-07' AND reservationfrom <= '2016-07-09'
    group by hotelid
) tm on tm.hotelid=ro.hotel
WHERE
  ac.active = 1 AND ac.delete = 0
GROUP BY
  ac.id
Comments