Getting the missing dates from a sql table

Question

I have a mysql table with the rows: ID, name, startDate, endDate.

As a rule, the dates should be consecutive and i want to alert the user if an interval is missing.

Saying i have this dates inserted:

2012-03-25 -> 2012-03-29
2012-04-02 -> 2012-04-05

I wanna show a message like

"No dates found from 2012-03-29 to 2012-04-02. Please insert data for this interval"

Can this be done without surfing with php the entire table entries?

Thanks!

Answer 1

SELECT t1.endDate AS gapStart, (SELECT MIN(t3.startDate) FROM `table` t3 WHERE t3.startDate > t1.endDate) AS gapEnd
FROM `table` t1
LEFT JOIN `table` t2
    ON t1.endDate = t2.startDate
WHERE t2.startDate IS NULL