[RESOLVED] Complex (for me) query - finding MAX with JOIN?

bunner_bob

I'm trying to query a join of two tables, and do it in a single query if possible (rather than one query, then a second one using results from the first). Forgive my bone-headedness - I still have a ways to go to grok sql...

(simplified)
Table 1: Events
EventID
Title
FollowupText

Table 2: EventDates
DateID
EventID
Date

Each event has multiple dates. I want to select the title, description, and LATEST date for each event. I started with:

SELECT DISTINCT Events.* FROM Events, EventDates WHERE EventDates.EventID=Events.EventID

but how do I tell it WHICH Date I want it to use? I want the MAX date, but I tried

SELECT MAX(EventDates.Date) AS LastDate, Events.EventID, Events.Title, Events.FollowupText FROM Events LEFT JOIN EventDates ON Events.EventID=EventDates.EventID

(based on reading some examples from other posts on PhpBuilder) and get a fatal error. It seems I can select just MAX(EventDates.Date), but as soon as I add any Events fields to the Select MySQL doesn't like it.

I'd also like to sort the results by LastDate, but once I get the query working that shouldn't be too difficult.

If possible I'd like to do it without subqueries. The host for this site is using MySQL 4.x but I haven't upgraded my local dev machine yet - still 3.x here.

Lars_Berg

 SELECT 
    MAX(EventDates.Date) AS LastDate, 
    Events.EventID, 
    Events.Title, 
    Events.FollowupText 
  FROM Events 
    LEFT JOIN 
       EventDates ON Events.EventID = EventDates.EventID 
 group by 
    Events.EventID, 
    Events.Title, 
    Events.FollowupText

mlat

I just came on here looking for a solution to a very simular problem to yours, I don't know if anyone can help me out as well.

What I need is generally the same kind of thing, except the "EventDates" using the example above needs to only return the record with the highest value of EventDates.Date.

Whenever I try something like your example Lars Berg, all I get is every single entry that I have in EventDates. I also must left join from another table if this helps, but thats dependant on what the highest entry returned would be.

bunner_bob

That's beautiful Lars - the Group By is the secret. Without it it's just broken, apparently.

Mlat - I'm only getting one Date per Event with the above query, and that date is the highest one belonging to the event. I messed around with adding dates later and earlier than the previous "max" and the query continued to work.

If you're trying to get the single highest date for ALL entries, try "Order By LastDate DESC" and either Limit 1 (assuming this works with the Max and Group By) or just read the first result record ($result_array[0]) and you should have it.

mlat

I have tried LIMIT 1 but it limits the number of results to 1, not per group.

And I could do your second suggestion, but that can be a rather taxing solution on system resources.