Query: select all rooms which are not booked I have a simple room booking system consisting of following tables:

booking
| id | user | begin | end | 

room
| id | name |

booking_map
| id | room_id |


I can list all rooms that are booked at the moment with the following query:

SELECT booking.id,booking.user,booking.begin,booking.end,room.name 
FROM booking,booking_map,room WHERE ((booking.id = booking_map.id)
AND (room.id = booking_map.room_id) AND (begin &lt; now()) AND (end &gt; now()));


But how would I list all rooms that free?
Let's look at some example data:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE booking
    (`id` int, `user` varchar(2), `begin` datetime, `end` datetime)
;

INSERT INTO booking
    (`id`, `user`, `begin`, `end`)
VALUES
    (1, 'u1', '2013-02-03 00:00:00', '2013-02-04 00:00:00'),
    (2, 'u2', '2013-02-03 00:00:00', '2013-02-06 00:00:00'),
    (3, 'u3', '2013-02-05 00:00:00', '2013-02-07 00:00:00'),
    (4, 'u4', '2013-02-10 00:00:00', '2013-02-11 00:00:00')
;

CREATE TABLE room
    (`id` int, `name` varchar(2))
;

INSERT INTO room
    (`id`, `name`)
VALUES
    (1, 'r1'),
    (2, 'r2'),
    (3, 'r3'),
    (4, 'r4'),
    (5, 'r5'),
    (6, 'r6')
;

CREATE TABLE booking_map
    (`id` int, `room_id` int)
;

INSERT INTO booking_map
    (`id`, `room_id`)
VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 1)
;


First we need to find all bookings for the day:

Query 1:

SELECT * 
  FROM booking
 WHERE begin &lt;= '2013-02-04'
   AND end &gt;=   '2013-02-04'


[Results][2]:

| ID | USER |                           BEGIN |                             END |
|----|------|---------------------------------|---------------------------------|
|  1 |   u1 | February, 03 2013 00:00:00+0000 | February, 04 2013 00:00:00+0000 |
|  2 |   u2 | February, 03 2013 00:00:00+0000 | February, 06 2013 00:00:00+0000 |


Or really, we need a list of all booked rooms for the day. For that we just join booking to booking_map:

Query 2:

SELECT m.room_id
  FROM booking AS b
  JOIN booking_map AS m
    ON b.id = m.id
 WHERE b.begin &lt;= '2013-02-04'
   AND b.end &gt;=   '2013-02-04'


[Results][3]:

| ROOM_ID |
|---------|
|       1 |
|       2 |


Now, with that information we can just return all rooms that are not in the list of booked rooms:

Query 3:

SELECT *
  FROM room AS r
 WHERE r.id NOT IN(
    SELECT m.room_id
      FROM booking AS b
      JOIN booking_map AS m
        ON b.id = m.id
     WHERE b.begin &lt;= '2013-02-04'
       AND b.end &gt;=   '2013-02-04'
   )


[Results][4]:

| ID | NAME |
|----|------|
|  3 |   r3 |
|  4 |   r4 |
|  5 |   r5 |
|  6 |   r6 |