SQLite sub-query not working

Table Name: MRM_BOOKING_DETAILS
Sample Data:

Booking_id USER_ID ROOM_ID CHECKIN             CHECKOUT
1          hary    1       2012-04-16 09:00:00 2012-04-16 09:30:00
2          jenny   2       2012-04-17 13:00:00 2012-04-17 13:30:00
3          steve   1       2012-04-16 15:00:00 2012-04-16 15:30:00

and so on...

Booking_id is the Primary Key. Room_Id is the Foreign Key w.r.t. the Room_Details_Table having Room_Id as PK.

My target is to get the list of records available on a particular day, particular time entered by the user. For doing this i came up with this query:

SELECT ROOMNO,BUILDINGNO 
FROM MRM_ROOM_DETAILS 
WHERE ROOMID IN (SELECT distinct roomid 
                FROM MRM_BOOKING_DETAILS 
                WHERE (CHECKIN NOT BETWEEN '2012-04-13 09:50:00' AND '2012-04-13 10:20:00')
                  and (CHECKOUT NOT BETWEEN '2012-04-13 09:50:00' AND '2012-04-13 10:20:00'))
  AND CAPACITY > 15 AND PROJECTIONSTATUS = 'NO';

Capacity and ProjectionStatus are columns in Room details table, please ignore that part as of now.

CHECKIN and CHECKOUT are the values provided by the user in search screen.

But unfortunately the sub-query is not correct, hence I am not getting the desired results.

SELECT distinct roomid 
FROM MRM_BOOKING_DETAILS 
WHERE (CHECKIN NOT BETWEEN '2012-04-13 09:50:00' AND '2012-04-13 10:20:00') and
      (CHECKOUT NOT BETWEEN '2012-04-13 09:50:00' AND '2012-04-13 10:20:00');

Here's why I say the sub-query is not correct. Suppose the values provided by the user are:

CHECKIN : 2012-04-16 09:00:00 and CHECKOUT: 2012-04-16 09:30:00

With the above data in the table, both ROOM_ID 1 and 2 will be returned. For the first row, it will fail, but it will succeed for the second and third row as a result it will return both 1 and 2 which is not correct. The correct result should be only 2.

Please help me to get the correct query.

Solution

Change your logic from "rooms that are booked at times other than x" to "rooms that are not booked at x time."

Also, you should probably use the DATETIME function.

UPDATE

I've added checking for all intersections, and am not using the inclusive BETWEEN so that a room may be booked from 8:30 to 9:00 and from 9:00 to 9:30.

SELECT ROOMNO, BUILDINGNO 
FROM MRM_ROOM_DETAILS 
WHERE ROOMID NOT IN (
    SELECT DISTINCT ROOM_ID as ROOMID
    FROM MRM_BOOKING_DETAILS 
    WHERE (DATETIME(CHECKIN) > DATETIME('2012-04-16 08:30:00') AND DATETIME(CHECKIN) < DATETIME('2012-04-16 09:31:00'))
        OR (DATETIME(CHECKOUT) > DATETIME('2012-04-16 08:30:00') AND DATETIME(CHECKOUT) < DATETIME('2012-04-16 09:31:00'))
        OR (DATETIME('2012-04-16 08:30:00') > DATETIME(CHECKIN) AND DATETIME('2012-04-16 08:30:00') < DATETIME(CHECKOUT))
        OR (DATETIME('2012-04-16 09:31:00') > DATETIME(CHECKIN) AND DATETIME('2012-04-16 09:31:00') < DATETIME(CHECKOUT))
)
    AND CAPACITY > 15
    AND PROJECTIONSTATUS = 'NO';