Ok..my table formats are as such:
Events |
EventName varchar(25) NOT NULL default '',
EventDate date NOT NULL default '0000-00-00',
EventID int(11) NOT NULL default '0',
PRIMARY KEY (EventID)
) TYPE=MyISAM |
MemberEvents |
Name text NOT NULL,
MemberID tinyint(4) NOT NULL default '0',
EventID int(11) NOT NULL default '0',
PRIMARY KEY (MemberID,EventID)
) TYPE=MyISAM |
Members |
MemberID int(12) NOT NULL auto_increment,
Name varchar(25) NOT NULL default '',
Realname text NOT NULL,
Year year(4) NOT NULL default '0000',
Exterior text NOT NULL,
Interior text NOT NULL,
Profile text NOT NULL,
Drives text NOT NULL,
Dinners text NOT NULL,
Show text NOT NULL,
Tech text NOT NULL,
Track text NOT NULL,
Other text NOT NULL,
Email text NOT NULL,
Clubmail text NOT NULL,
Firstevent text NOT NULL,
Firsteventdate text NOT NULL,
Lastevent text NOT NULL,
Lasteventdate text NOT NULL,
Events smallint(6) NOT NULL default '0',
Type text NOT NULL,
PRIMARY KEY (MemberID)
) TYPE=MyISAM |
And my latest query is this:
SELECT MemberEvents.Name,
DATE_FORMAT( MIN( Events.EventDate ) , '%m/%d/%y' ) AS FirstEventDate,
Events.EventName, MIN( Events.EventID ) AS FirstEvent,
DATE_FORMAT( MAX( Events.EventDate ) , '%m/%d/%y' ) AS LastEventDate,
MAX( Events.EventID ) AS LastEvent,
COUNT( Events.EventID ) AS TotalEvents
FROM MemberEvents
INNER JOIN Events ON MemberEvents.EventID = Events.EventID
GROUP BY MemberEvents.Name
ORDER BY TotalEvents DESC
And the output is like this:
So the only thing I can't figure out is how to make the "Last Event" column output the name of the last event, instead of the ID number of the last event.
How do I compare the number produced in the query (LastEvent) to the EventID of the Events table in order to output the corresponding EventName? That is my problem.....
Any ideas?
JJ
