Hello everybody, I have following problem to solve...

There are 3 possible states, open, closed, sold out.
I need to select hotels where in the given dates the state is open in whole date. If there is even one day closed and all rooms in given hotel are closed, hotel shouldn't be selected.

Now I have something like:
SELECT * FROM hotels WHERE id NOT IN (SELECT id_hotel FROM accommodation_states WHERE fromDate <= ? AND toDate >= ? AND state = 2);

Now a problem occurs. This disables hotels where even only one room is closed and all others can be opened. That's wrong.

An introduction, tables:
Code:
mysql> describe accommodation_states
+--------------+---------+------+-----+----------+----------------+
| Field           | Type    | Null | Key | Default | Extra            |
+--------------+---------+------+-----+----------+----------------+
| id               | int(11) | NO   | PRI | NULL    | auto_increment |
| id_hotel     | int(11) | NO   |       | NULL    |                      |
| id_room     | int(11) | NO   |       | NULL    |                      |
| fromDate   | date     | NO  |       | NULL     |                     |
| toDate       | date     | NO  |       | NULL     |                     |
| state         | int(11)  | NO  |       | NULL     |                     |
+-------------+----------+------+-----+----------+----------------+


mysql> describe hotels;
+---------------------------+----------------+-------+-----+----------+----------------+
| Field                           | Type             | Null  | Key | Default | Extra            |
+---------------------------+----------------+-------+-----+----------+----------------+
| id                               | int(11)           | NO   | PRI | NULL    | auto_increment |
| name                         | varchar(255) | NO   |        | NULL    |                     |
| ...
| publish                       | tinyint(1)      | NO   |         | 0         |                     |
+---------------------------+----------------+------+-------+---------+----------------+


Example of data:

Code:
 mysql> select * from accommodation_states where state = 2 and fromDate <= '2009-03-25' and toDate >= '2009-03-26';
+------+----------+---------+--------------+----------------+-------+
| id   | id_hotel | id_room | fromDate  | toDate         | state |
+------+---------+---------+---------------+----------------+-------+
| 1925 |       93 |      16 | 2009-03-21 | 2009-05-01 |     2 |
| 1879 |     153 |    368 | 2009-03-24 | 2009-03-28 |     2 |
| 2237 |       36 |      59 | 2009-03-10 | 2009-05-26 |     2 |
| 2240 |       36 |      60 | 2009-03-10 | 2009-05-26 |     2 |
| 2243 |       36 |      61 | 2009-03-10 | 2009-05-26 |     2 |
| 2392 |       88 |      69 | 2009-03-10 | 2009-06-30 |     2 |
| 2394 |       88 |      71 | 2009-03-10 | 2009-06-30 |     2 |
| 2396 |       88 |      70 | 2009-03-10 | 2009-06-30 |     2 |
| 2398 |       88 |      72 | 2009-03-10 | 2009-06-16 |     2 |
| 3056 |       40 |      53 | 2009-03-15 | 2009-05-28 |     2 |
| 3255 |      114 |     102 | 2009-03-23 | 2009-03-28 |     2 |
+------+----------+---------+------------+------------+-------+
Now all these hotels are closed no matter what. They should be closed only when all rooms are in state 2 ...