5 Table Join

I am trying to join 5 tables in a sql server 2k db. Does anyone know of a good set of guidelines for doing this? Alternately, could someone find the problem in the following query?

The query that I am using is listed here (please let me know if I am violating any programming guidelines on this):

SELECT p.ParticipantID, pr.Age, ir.FnlTime, e.EventDate
FROM Participant p INNER JOIN PartRace pr ON p.ParticipantID = pr.ParticipantID
JOIN IndResults ir ON pr.ParticipantID = ir.ParticipantID
JOIN RaceData rd ON ir.RaceID = rd.RaceID
JOIN Events e ON e.EventID = rd.EventID
WHERE rd.Dist = '5_km' AND p.Gender = 'm' AND ir.FnlTime <> '00:00' AND e.EventGrp = 1
ORDER BY ir.ParticipantID

The problem that I am having is that if a participant shows up multiple times (which they could do since this is designed to get the performances for an event over a series of years) it does not associate the correct data from year to year. Basically some times show up where they shouldn't.http://www.dbforums.com/showthread.php?t=1196943|||Your code looks fine, apart from the gratuitous and obfuscatory use of aliases.

INSERT RIGHTEOUS AND INDIGNANT CANADIAN COMMENT FROM RUDY HERE
I don't understand what you mean by "does not associate the correct data from year to year". Do you need additional joins on the tables to link YEARs?
Also, what is the datatype of FnlTime? This clause: "...AND ir.FnlTime <> '00:00'" looks a little odd.|||Somewhere you need to be storing the date. Logical places might be the Race or Event tables. You need to include that date in the foreign key, which implies you need to carry it forward into the ON clauses.

-PatP|||Thanks to both of you. It is now fixed. The event date is in the events table. How would you use aliases?

Thanks!|||I don't, unless I am referencing a table twice. I think it makes code difficult to read because somebody trying to debug it has to look way down at the bottom of the statement to interpret the column and table references at the top of the statement.
The use of non-descriptive aliases takes you one giant step further away from self-documenting code. And when I am reviewing code I want to be able to conecntrate on what the code is doing, and not spend time trying to figure out what it is.|||Also, I try to use abreviations of the table names in the column names.
For example if you had a table with race tracks, the column for the first address field would be trkAddress1. If the table were for drivers, the column would be drvrAddress1. This way there is no confusion which address I was looking at.|||Ugh. So you have fields called SupplierCity in one table, EmployerCity in another, BusinessCity in another, etc? I certainly don't like that idea. The repetition of table names in column names is completely redundant if you qualify your column references, as you should.

For long-time supportable code, avoid mixing the logical structure of the database (names) with the physical structure (object type or location).|||Ugh. Having trkAddress1 and drvrAddress1, for example, also makes it easier for using Crystal Reports.|||Apply aliases in the output recordset.

And nothing makes Crystal Reports easy. It get a double-ugh.|||Sorry, I meant to say that it makes it easier for clients who use the database and crystal where they want to be able to create reports dynamically. I understand your point though and it is a valid one. I dislike Crystal Reports as well.

I like your signature by the way.