A bit complicated query, how to?

[Douglish]

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:

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:

 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 ...

[Ron Weller]

You need to reverse your logic and look for open rooms for your date. Try this:

SELECT * FROM hotels WHERE id IN (SELECT id_hotel FROM accommodation_states WHERE fromDate >= ? AND toDate <= ? AND state = 1);

[Douglish]

Thank you very much for you reply. I have tried this, but there is another problem with this solution.

For example client selects date 2009-03-20 - 2009-03-25

In databaase is saved:

2009-03-10 - 2009-03-23 state: 1
2009-03-24 - 2009-03-26 state: 2

In this case it would be presented as opened, anyway there is one day closed (2009-03-24) and it means it can't be displayed.

(Last day (check-out) isn't important for this.)

[Ron Weller]

Ok you are using a date range so the fromDate must be <= your start date and the toDate needs to be >= your end date and the state must = 1 which indicates the fromDate and toDate are open.

So your original Date logic was correct, but the state needed to be 1 and the id needed to be 'In' the subquery where you were doing 'Not In' before.

This version should now work:
SELECT * FROM hotels WHERE id IN (SELECT id_hotel FROM accommodation_states WHERE fromDate <= ? AND toDate >= ? AND state = 1);