mysql - search between dates where all dates appear

Question

I'm working with some imported data that stores details about whether a "room" is available on a specific day or not. Each room has an individual entry for the date that it is available.

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

A user can search across a date range and the search needs to bring back the relevant rooms that are available between those two dates.

In other words using a sql query to search:

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

would not suffice as this would bring back all rooms available at SOME point between the chosen dates not the rooms that are available for ALL of the dates concerned.

I am considering using a temporary date table in some way to cross-reference that there is an entry for every date in the range but are uncertain as to the best way to implement this.

The end code platform is PHP and I'm also exploring whether the data can be processed subsequently within the code but would like to keep everything with the sql if possible.

Any suggestions that put forward would be greatly appreciated.

Thanks

Answer 1

Provided that (id, date) combination is unique:

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

Make sure you have a UNIQUE constraint on (id, date) and the date is stored as DATE, not DATETIME.

Answer 2

Update: my original answer was identical to Quassnoi's but 1 minute too late, so I decided to delete it and do something different instead. This query does not assume that (id, date) is unique. If there is more than one entry, it selects the cheapest. Also, it also sums the total cost and returns that too which might also be useful.

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