knotty mysql query -- please help!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nufferkay
Forum Newbie
Posts: 24
Joined: Fri Nov 28, 2003 2:27 pm

knotty mysql query -- please help!

Post 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
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
nufferkay
Forum Newbie
Posts: 24
Joined: Fri Nov 28, 2003 2:27 pm

Post 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....
Post Reply