Problem in room search

Question

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