I'm trying to join two tables and group the results. The tables are joined on an EventID field. The basic query gets all upcoming events for the following month:
SELECT StartDate, EndDate, StartTime, PresentedBy, EventDescription, AdditionalInfo, VendorEventID, FeaturedText
FROM EventCalendar, Events
WHERE EventCalendar.EventID = Events.EventID AND StartDate >= NOW() AND StartDate < DateAdd('d', 31, NOW())
I want to group the events so that all performances of a particular event will be together, and the events will be ordered by date. The problem I'm having is when an event has dis-contiguous performances and a different event falls in the middle of the other event's dates. For example, if there are four consecutive performances:
Vocal concert (Events.EventID 5) 12/1/10
Band concert (Events.EventID 2) 12/2/10
Vocal concert (Events.EventID 5) 12/3/10
Orchestra concert (Events.EventID 3) 12/4/10
I want to group them instead as:
Vocal concert (Events.EventID 5) 12/1/10
Vocal concert (Events.EventID 5) 12/3/10
Band concert (Events.EventID 2) 12/2/10
Orchestra concert (Events.EventID 3) 12/4/10
Note that I can't order them by the EventID, because it's most likely not consecutive.
For the life of me, I can't figure out how I can group them. I know that to use GROUP BY Events.EventID, I have to use an aggregate function, but which, and how? Thanks!