Page 1 of 1

knotty mysql query -- please help!

Posted: Fri Nov 28, 2003 4:48 pm
by nufferkay
OK, I'm getting really frustrated. I know there's got to be a way to do this in one query:

I have two tables, Events and Occurrences, in a one-to-many relationship linked on the event_id and occ_event_id fields.

For each event, I need to find the occurrence with the smallest occ_date >= today's date, and I need to order the events by that next date.

I'm using MySQL 3.x, so I know that restricts things a bit.

TIA for any help you can give me!

-N

Posted: Fri Nov 28, 2003 5:06 pm
by microthick
If you could use nested selects, the solution would be pretty simple. But I don't think MySQL 3 can support that.

I'll think about this more.

Posted: Fri Nov 28, 2003 5:13 pm
by microthick
Ok, here's my easiest solution without doing multiple loops.

1) Select all occurrences for all events where min date > today's date, ordered by date ascending.
2) Loop through the results.
3) You'll be maintaining an array of EventIDs that you've already shown info for. At every iteration of your loop, see if the EventID is in that array of EventIDs. If it is, don't show. Otherwise, show.

Note that this will only work if you only need to show info about the min date occurrence for any given event.

Posted: Fri Nov 28, 2003 6:32 pm
by nufferkay
Thanks, Microthick - you've just given me an idea - I think it can be done in just two queries:

SELECT occ_id
FROM Occurrences
WHERE occ_date >= CURDATE()

Dump the results of this query into a comma-separated list string $occ_list which will look like this (or something to that effect): '12, 3, 15, 24, 6'

SELECT Events.*, MIN(occ_date) as next_date
FROM Events, Occurrences
WHERE occ_id IN ($occ_list)
AND event_id=occ_event_id
GROUP BY occ_event_id
ORDER BY next_date

I'm going to have to try this as soon as I get a chance...

Thanks for your help!
-N

EDIT: Whoot! it worked! good stuff....