Hi,
I am working hotel reservation project, I have a problem in searching room in the following table.
Table customer:
Client_id(pk) firstname lastname
praveenfds Praveen kumar
Table Reservationdetials:
member_id (pk) client_id(fk) start_date end_date
1001 praveenfds 2011-10-17 00:00:00 2011-10-20 00:00:00
Table Reservation:
reserve_id (pk) member_id(fk) room_id(fk)
RCV1 1001 ROOM101
Table room:
room_id(pk) room_number room_categId(fk)
ROOM101 101 RC1
ROOM102 102 RC1
ROOM103 103 RC1
ROOM104 104 RC1
ROOM105 105 RC1
ROOM201 201 RC2
ROOM202 202 RC2
ROOM301 301 RC3
ROOM302 302 RC3
Table roomcategory:
room_categId(pk) room_category room_rate persons_allowed
RC1 Standard 2500.00 3
RC2 Deluxe 3500.00 4
RC3 Suites 4500.00 5
My query is
room ROOM101 is booked on 17-10-2011 to 20-10-2011 by praveenfds(client_id) in the reservationdetails and reservation table mentioned above.
SELECT rooms.room_id,cat.room_category,cat.room_rate,cat.persons_allowed
FROM room rooms
INNER JOIN roomcategory cat ON rooms.room_categId = cat.room_categId
WHERE cat.room_category = 'Standard'
AND rooms.room_id not IN (SELECT t1.room_id
FROM room t1
INNER JOIN reservation t2 ON t1.room_id = t2.room_id
INNER JOIN reservationdetails t3 ON t2.member_id = t3.member_id
WHERE not(('2011-10-16' between t3.start_date and t3.end_date) and
('2011-10-21' between t3.start_date and t3.end_date)))
Result for the above query:works
ROOM102 Standard 2500.00 3
ROOM103 Standard 2500.00 3
ROOM104 Standard 2500.00 3
ROOM105 Standard 2500.00 3
but if i give
(SELECT t1.room_id
FROM room t1
INNER JOIN reservation t2 ON t1.room_id = t2.room_id
INNER JOIN reservationdetails t3 ON t2.member_id = t3.member_id
WHERE not(('2011-10-17' between t3.start_date and t3.end_date) and
('2011-10-18' between t3.start_date and t3.end_date)))
it displays all the rooms since given date is booked.
I tried > and < also but not getting the solution.
Please solve my query, if this logic is incorrect please provide any suggestions.
Thanks
Praveen