SQL query to exclude certain values from table

Question

Hi everyone I am in the process of building a room allocating system that checks if a room is available on a given date. There are about 20 unique room ids and I've just included as snippet of several above.

So for example if the user types in the date of 2012-01-01 I would want the room_id of 1 to be excluded from the results because as seen above it has been already booked for that date.

In a sense any match found between the user's in-putted date should cause the entire list of corresponding room_id's to be excluded from the list.

I used the below SQL query:

SELECT DISTINCT room_id
FROM room_booking
WHERE date_booked<>'$date'

Unfortunately it doesn't work because although it excludes the result for a single row, it includes the rest of the results for a given room_id?

Answer 1

Not tried this, but here goes

SELECT DISTINCT room_id
FROM room_booking
WHERE room_id NOT IN 
    (SELECT DISTINCT room_id
     FROM room_booking
     WHERE date_booked = '$date'
    )

Answer 2

You could use a NOT EXISTS

SELECT DISTINCT room_id
  FROM room_booking rb1
 WHERE NOT EXISTS( SELECT 1
                     FROM room_booking rb2
                    WHERE rb2.date_booked = <<date>>
                      AND rb2.room_id     = rb1.room_id )

or a NOT IN

SELECT DISTINCT room_id
  FROM room_booking
 WHERE room_id NOT IN (SELECT room_id
                         FROM room_booking rb2
                        WHERE rb2.date_booked = <<date>>)