Problem in room search

praveen bahubali

15y1.9kSQL

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

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Javeed M Shaikh15y ago
Accepted answer
Hi Praveen,

I think I finally got this, please try the following:

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 start_date <='2011-10-24' and end_date >='2011-10-21')

Remember that the date compared with start_date is the enddate variable and with the end_date is the startdate variable.

Please do not forget to mark "Accepted Answer".
0
praveen bahubali15y ago
Thank you Javeed, thank you very much.
This works....
0
praveen bahubali15y ago
Javeed, i already tried this and check the above which i post first.
This will work only the dates from 17-10-2011 to 20-10-2011.

But when we think logic wise if the hotel is booked on 17 to 20, and some one try to book hotel on 15 to 18 or 19 to 21. it should not show right!!!
0
Javeed M Shaikh15y ago
hi Praveen,

Please try this and let us know:

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 ((('2011-10-16' between start_date and end_date) and
('2011-10-21' between start_date and end_date)) OR
(start_date >='2011-10-16' and end_date<='2011-10-21')))

Please do not forget to mark "Accepted Answer".
0
Javeed M Shaikh15y ago
Suthish,

The script with data is attached by Praveen, just scroll up a few post.
0
Suthish Nair15y ago
Praveen, can you attach the table scripts with sample data.
0
praveen bahubali15y ago
This result is ok for this inputs '2011-10-17 and 2011-10-19','2011-10-16 and 2011-10-21'

But i want both conditions to be satisfied in this query.

if the inputs not in booked dates, it should show all the rooms.
ex:
2011-10-13 and 2011-10-16
2011-10-21 and 2011-10-24

Result:
ROOM101    Standard    2500.00    3
ROOM102    Standard    2500.00    3
ROOM103    Standard    2500.00    3
ROOM104    Standard    2500.00    3
ROOM105    Standard    2500.00    3
0
praveen bahubali15y ago
Hi Javeed,

Query1 is perfect for this input
(('2011-10-16' between t3.start_date and t3.end_date) and ('2011-10-21' between t3.start_date and t3.end_date))
since ROOM101 is booked on 2011-10-17 to 2011-10-20

Result:(Query1)
ROOM102Standard2500.003
ROOM103Standard2500.003
ROOM104Standard2500.003
ROOM105Standard2500.003

i want the same result(Query1) for the following input 2011-10-17 and 2011-10-19

And also if the inputs not in booked dates, it should show all the rooms.
ex:
2011-10-13 and 2011-10-16
2011-10-21 and 2011-10-24

Result:
ROOM101    Standard    2500.00    3
ROOM102    Standard    2500.00    3
ROOM103    Standard    2500.00    3
ROOM104    Standard    2500.00    3
ROOM105    Standard    2500.00    3

But i am not getting this result.

0
Javeed M Shaikh15y ago
with the following query1:

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

Following is the output

ROOM102Standard2500.003
ROOM103Standard2500.003
ROOM104Standard2500.003
ROOM105Standard2500.003

With the following Query2:
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))

Following is the output
No Records Found.

But with the following query3:
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))

Following is the output:
ROOM101

Please elaborate on what output is expected in Query 2

Please do not forget to mark "Accepted Answer".

0
praveen bahubali15y ago
Hi Javeed,
Thanks in advance, i have attached the file here.

Thanks
Praveen
0
Javeed M Shaikh15y ago
can you please attach the script to create these tables and data, I will try on my local server and give you the query.

Please do not forget to mark "Accepted Answer".
0
praveen bahubali15y ago
Hi, I tried using date conversion also, Can u please edit with the above code. Thanks Praveen
0
Javeed M Shaikh15y ago
Hi Praveen,

Please try using conversion of date in your where clause by using CAST and try again.

Please do not forget to mark "Accepted Answer".
0