Burhan Khalid wrote: > Phil Turmel wrote: >> >> SELECT DATE_ADD(before.end_date,INTERVAL 1 DAY) AS gapstart, >> MIN(DATEDIFF(after.start_date,before.end_date)-1) AS gapsize >> FROM (SELECT events.id, events.end_date >> FROM `events` INNER JOIN halls_events >> ON events.id=halls_events.eventid >> WHERE events.end_date > CURDATE() >> AND halls_events.hallid=$hall) >> AS `before`, >> (SELECT events.id, events.start_date, events.end_date >> FROM `events` INNER JOIN halls_events >> ON events.id=halls_events.eventid >> WHERE events.end_date > CURDATE() >> AND halls_events.hallid=$hall >> UNION SELECT 0 AS id, >> DATE_ADD(CURDATE(),INTERVAL 1 YEAR) as start_date, >> DATE_ADD(CURDATE(),INTERVAL 1 YEAR) as end_date) >> AS `after` >> WHERE after.end_date>before.end_date >> GROUP BY DATE_ADD(before.end_date,INTERVAL 1 DAY) >> HAVING gapsize>$gapneeded >> > > Phil: > > Thanks again for your SQL wizardy, this really is wayyy beyond my SQL > skills. It works brilliantly!! I wasn't aware that you can alias entire > result sets (as you did with `before` and `after`). > > One question -- what does UNION SELECT 0 AS id do? This is the only > thing that I cannot understand. > > Now to digest all this so hopefully next time I can write something as > elegant as this myself. > > Again, thank you very much. You really saved me a lot of headaches. > Burhan, Re: SQL wizardry. Glad to help. I suspect you can pick this up more easily if you think in terms of temporary tables... Where older MySQL and other lesser DB engines might have you solve a problem with one or more temporary tables, MySQL 4.1+ and pretty much all commercial DBs let you use a subquery in the FROM clause instead. Re: UNION. The main query above compares every row from `before` with every row from `after`, computing the gap between them. It uses grouping to find the smallest gap after each `before` row. To get a final result after the last real event, the union operation blends one extra row into the `after` table, creating a virtual event one year into the future. This gives something to compare to the last row in `before`. Thinking about it some more (2nd day, clearer head), there should be a similar UNION on the `before` table, creating a virtual row for the current date. That covers the case where the hall is unused today, and the desired gap starts tomorrow. I'll leave that one for you, if you like. Regards, Phil ps. If you are scheduling halls far into the future, you might want to change the INTERVAL to something larger than one year.