I have been searching and experimenting with this for 2 days and am unable to get it to work properly and am really hoping for some help.
cabins
cabin_id
name
active
start_day (enum 'Saturday' or 'Sunday')
bookings
booking_id
cabin_id
check_in (date)
check_out (date)
confirmed
So far pretty simple. I should note that cabin rentals are only in 1 week blocks and the week either starts on a Saturday or Sunday.
I need to return all of the 'cabins' that have ANY availability between two dates, not that ARE available between two dates as with most reservation system answers I've seen on here.
My attempt so far:
SELECT c.cabin_id, c.name
FROM cabin c
WHERE (c.cabin_id NOT IN (
SELECT b.cabin_id
FROM bookings b
WHERE (b.check_out >= '$date_from' && b.check_in <= '$date_to') OR
(b.check_out <= '$date_from' && b.check_in >= '$date_to')
)
)
ORDER BY `c`.`order` ASC
This only shows the cabins which have no bookings at all between the two dates.
For example I added a booking from Jan 26, 2014 to March 2, 2014 and this query will not show the cabin in January, February or March where it should show it in January and March as it has some availability still in those months.
I've worked myself around in a loop now and can't seem to get my head around this.
EDIT SQL Fiddle with tables and data: http://sqlfiddle.com/#!2/e3c8f/3
