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

Joshua Olson joshua at waetech.com
Tue May 31 11:08:25 CDT 2005


> -----Original Message-----
> From: Phil Turmel
> Sent: Tuesday, May 31, 2005 9:23 AM
> 

> SELECT before.end_date, after.start_date
>      FROM (SELECT events.id, events.end_date
> 	FROM events INNER JOIN hall_events
> 	    ON events.id=hall_events.eventid
> 	WHERE events.end_date > CURDATE()
> 	    AND hall_events.hallid=$hall)
> 	AS before,
> 	(SELECT events.id, events.start_date
> 	FROM events INNER JOIN hall_events
> 	    ON events.id=hall_events.eventid
> 	WHERE events.end_date > CURDATE()
> 	    AND hall_events.hallid=$hall)
> 	AS after
>      WHERE DATEDIFF(after.start_date,before.end_date)>7
>      ORDER BY DATEDIFF(after.start_date,before.end_date)
>      LIMIT 1
> 
> ** Untested **. needs MySQL v4.1 or later

Phil,

Allow me to rewrite your query, substituting some lines to make it easier to
understand.

SELECT before.end_date, after.start_date
     FROM ([All Events After Right Now For $hall]) AS before,
	([All Events After Right Now For $hall]) AS after
     WHERE DATEDIFF(after.start_date,before.end_date)>7
     ORDER BY DATEDIFF(after.start_date,before.end_date)
     LIMIT 1

Now the query is much less complex.  It's essentially comparing all upcoming
events against ALL other upcoming events.

I'm not sure this is going to return what you want, ultimately.

Let's say that there are two events for a hall that are separated by 14
days.  Conceptually, that will be returned by this query even if every day
in between them is booked by other events.

?

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/




More information about the thelist mailing list