Mysql query which needs to do calculations

Databases
1

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!)
hostels

max_guests

bookings

hostel_id
check_in
check_out
guests

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))
LIMIT 10

Can anyone tell me how I could modify that query to make it do what I need?

2

Hey there,

In case it encourages anyone to help me out I’ve stuck together a sqlfiddle: http://sqlfiddle.com/#!2/300f3/1

and a better description of my problem might be:

two tables: Hostels and Bookings.

Hostels have a maximum number of guests which they can accommodate. Bookings are made between two dates and have a column ‘guests’ for the number of guests in the booking.

Users should be able to search for a hostel given an area, a checkin date, a checkout date and the number of guests in the party.

My query at the moment finds all the hostels which don’t have a booking between the dates but what I actually want is:

The query to return all hostels where, if the hostel has bookings during the user specified period, then the sum of the guests involved in those bookings, subtracted from hostel.maxguests shows that there is enough space left in the hostel. IE the number needs to be more than the user specified guests number from the search.

3

I won’t claim to have looked at this in detail, but could you have something in your WHERE clause along the lines of:
“WHERE …max_beds - booked_beds >= number_in_party…”

Of course you need other stuff in the WHERE clause to limit the dates etc.