mysql query which needs to do calculations
I'm not so great with sql but I think what I want should be possible, I'm just not sure how to do it.
I have a site which allows users to list their youth hostel. Potential customers can then browse these hostels and place bookings.
Tables: (not including id's and other cols!)
When users search the site I allow them to select a checkin date, a checkout date, a place name, and the number of guests. Note that each hostel has a maximum number of guests which they can accommodate, for example 20.
So say previously a booking has been made at a particular hostel for 8 people during the dates selected for the search, then I need to know that the maximum number of guests that particular hostel can accommodate between those dates is now 12, and it would only appear in searches for <= 12 guests.
Likewise if that hostel had a couple more bookings and was at it's maximum capacity, then it shouldn't turn up in the search at all.
At the moment I have a query which gets all the hostels but if there's any bookings for a hostel between the selected dates, then it's excluded from the results. I'd like to be able to get all the bookings for each hostel during the selected dates, count the numbers of guests booked in, and if that number subtracted from the max_guests of the hostel gives a result which is lower than the number of guests specified in the search, then exclude it from the results.
at the moment my query looks like:
SELECT DISTINCT `Hostel`.`id`, `Hostel`.*, `Hostel`.`id`
FROM `hostelsdatabase`.`hostels` AS `Hostel`
LEFT JOIN `hostelsdatabase`.`bookings` AS `bookings` ON (`bookings`.`hostel_id` = `Hostel`.`id`)
LEFT JOIN `hostelsdatabase`.`users` AS `User` ON (`Hostel`.`user_id` = `User`.`id`)
WHERE ((`Hostel`.`address_one` LIKE '%london%') OR (`Hostel`.`address_two` LIKE '%london%') OR (`Hostel`.`city` LIKE '%london%'))
AND `Hostel`.`maxguests` >= 4
AND NOT EXISTS (SELECT * FROM bookings WHERE `bookings`.`hostel_id` = `Hostel`.`id` AND NOT('2013-07-31' > `bookings`.`checkout` OR '2013-08-02' < `bookings`.`checkin`))
Can anyone tell me how I could modify that query to make it do what I need?