How would you write a query to select the rooms which are booked between 2012-05-1 and 2012-11-01 from a booking database?
1
-
1What are your ideas? Show me your attempt, please.lszrh– lszrh2012-04-28 07:39:08 +00:00Commented Apr 28, 2012 at 7:39
Add a comment
|
3 Answers
select r.* from rooms r
left outer join bookings b on b.roomno = r.roomno
where b.dateto not between '2012-05-01' and '2012-11-01'
and b.datefrom not between '2012-05-01' and '2012-11-01'
or b.roomno is null
3 Comments
aleroot
And what happens if the booking period is in the middle of the start of the end date ? You don't get who have a room booked in the middle of start or end date ...
Chetter Hummin
@juergend May not work with rooms that have no bookings so far.
juergen d
@AmitBhargava: Thanks for the hint. I forgot to put the
left outer join in it. It has been in the SQLFiddle example though.This should include all rooms that aren't booked between the two given dates :
SELECT * FROM Room r
JOIN Booking b ON b.hotelNo = r.hotelNo AND r.roomNo = b.roomNo
WHERE NOT ('2012-05-01' BETWEEN (b.dateFrom AND b.dateTo)
OR '2012-11-01' BETWEEN (b.dateFrom AND b.dateTo))
Comments
Please try the following
SELECT * from room
WHERE (roomNo, hotelNo) not in
( select roomNo, hotelNo
from booking
where datefrom between '2012-05-01' and '2012-11-01'
or dateto between '2012-05-01' and '2012-11-01'
)