Need help on complex problem.

[AZDoc]

Almost There...
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:

http://www.s2000arizona.com/albums/Linked-Photos/aax.gif

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

[raf]

Welcome here.

i think you need to store the results of this query in a temporarely table and then join this table with the events table, to get the eventsname form both the first and last eventID.

[AZDoc]

raf,

you are right...but since I am a relative Newbie at this, I can't figure out the proper query to do that...


I've also tried this query:


SELECT Members.Realname, MIN(FirstEvents.EventName) AS FirstEventName,
DATE_FORMAT(MIN( FirstEvents.EventDate ),'%m/%d/%y') AS FirstEventDate,
MIN(FirstEvents.EventID) AS FirstEventID, MAX(LastEvents.EventName)
AS LastEventName, DATE_FORMAT(MAX( LastEvents.EventDate),'%m/%d/%y')
AS LastEventDate, MAX(LastEvents.EventID)
AS LastEventID, COUNT(Events.EventID) AS TotalEvents
FROM Members INNER JOIN MemberEvents ON Members.MemberID = MemberEvents.MemberID
INNER JOIN Events ON MemberEvents.EventID = Events.EventID
INNER JOIN Events AS FirstEvents ON Events.EventID = FirstEvents.EventID
INNER JOIN Events AS LastEvents ON Events.EventID = LastEvents.EventID
INNER JOIN MemberEvents AS FirstMemberEvents ON FirstEvents.EventID = FirstMemberEvents.EventID
AND Members.MemberID = FirstMemberEvents.MemberID
INNER JOIN MemberEvents AS LastMemberEvents ON LastEvents.EventID = LastMemberEvents.EventID
AND Members.MemberID = LastMemberEvents.MemberID
GROUP BY Members.Realname
ORDER BY TotalEvents DESC


but this doesn't work either....

This one just puts the First Event as the Alphabetical first, instead of the first event attended, the Last Event as the Alphabetical last instead of the last event attended....And neither of the event names correspond with the proper event ID....

I'm feeling pretty stumped..

any ideas how the right query might look?

Thanks
JJ

[raf]

Well, when i rethink it, the temporarely table isn't the best way.
f you have two variabels (like firsteventID and lasteventID) that both are foreign keys to the same table, then your inner join wount work. To be more precise, it will only work if the first and last eventID or the same. Else, it wount match.

Actuelly, this is more complex than i thought at first glance.

Now, normally, if your site has some traffic, you be displaying or querying this 'summary' table more then that your inserting records in the MemberEvents table.
So, from an efficienty point of view, i think you should build this summerizing table one, and then just update it with each new insert in MembersEvents.
But because of the problem i explained above, you'll need to include the names and data, and not the ID's. So it would actually be a seperate table, that isn't linked to the events table (can be linked to the members table --> so you should only include the member ID here and get the member data with a join).
This means that you'll need to be very careful about this table (to keep it accurate.) If you insert a new member --> you need to create a new record in this new table. Where you set the firsteventID etc to some defaultvalues. You need a countvariable in this table, that is initially set to 0
Then, when you insert a new record, you'll need to check the count. If it's 0, then you need to update both the fistevent variabels as the last eventvariabels and the countvariabel.
If it's higher then 0, you only need to update the lastevents-variabels and the count.

Now, this countvariabel could be used to check from time to time if the two tables are still siynchronised

If you do an update on the eventstable, you'll need to update the corresponding values in the new table.

So it will be tricky, but i don't immedeately see an (efficient)alternative. If you take care that the new tables is update when a new record is inserted in the MemberEvents table and updated when existing records from the Events table are updated, it shouldn't be a problem.
And it will return the sumerise tabel real fast, even if you'll get hundreds of member.
(+ keeping this table synchronised wount really affect the db-performance)