Mysql - search between dates where all dates are displayed

Question

I work with some imported data that stores data on whether the "room" is available on a specific day or not. Each issue has a separate entry for the date it is available.

| id  |  date        |  price  |
--------------------------------
|  1  |  2010-08-04  |  45.00  |

The user can search by date range, and the search must return the corresponding rooms that are available between the two dates.

In other words, using the sql query to search:

where date>=2010-08-04 AND date<=2010-08-09

not enough, as this will lead to the return of all rooms available at SOME points between the selected dates, and not rooms that are available for ALL relevant dates.

I am considering using a temporary date table in some way to cross-reference that there is a record for each date in the range, but is unclear as to the best way to implement it.

The final code platform is PHP, and I am also exploring whether data can be processed later in the code, but I would like to save everything using sql, if possible.

Any suggestions that have been put forward are welcome.

thank

Answer 1

: Quassnoi, , - . , (id, date) . , . , , .

SELECT id, SUM(price) FROM (
    SELECT id, date, MIN(price) AS price
    FROM Table1
    GROUP BY id, date) AS T1
WHERE `date` BETWEEN '2010-08-05' AND '2010-08-07'
GROUP BY id
HAVING COUNT(*) = DATEDIFF('2010-08-07','2010-08-05') + 1

Answer 2

, (id, date) :

SELECT  id
FROM    mytable
WHERE   date BETWEEN '2010-08-04' AND '2010-08-09'
GROUP BY
        id
HAVING  COUNT(*) = DATEDIFF('2010-08-09', '2010-08-04') + 1

, UNIQUE (id, date), date date, DATETIME.