Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 mysql query which needs to do calculations

Author  Topic 

barricades
Starting Member

2 Posts

Posted - 2013-07-31 : 06:21:29
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?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-31 : 07:00:28
We are on Microsoft SQL Server only here.
Any given solution can fail on MySQL...

Better try to get an answer in an MySQL-Forum


Too old to Rock'n'Roll too young to die.
Go to Top of Page
   

- Advertisement -