[thelist] Iterate through result set - Is this possible? (SQL)

[Phil Turmel]

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.