5 Table Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

5 Table Join

Author

Topic

bobbabuoy
Starting Member

33 Posts

Posted - 2006-03-01 : 07:02:37

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.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-01 : 07:21:39

Looks okay at first glance. Can you post your DDL (CREATE TABLE statements) so we can see what your entities look like. From what you've posted, there looks to be a bit of redundancy between IndResults and PartRace, but without seeing the other attributes, it's impossible to say. A bit of description of the entities would probably be helpful. I.e. does Event represent a specific instance of an event (i.e. '2006 Flora London Marathon') with EventGrp grouping them into the more generic events (i.e. 'London Marathon')?

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources