I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.
Thanks!
code:------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
------------------------what is the error you are getting?|||you have 4 tables, but what are the relationships and their cardinalities?
for example, Participant--PartReg is probably 1--m, but what about the others?
somewhere along the line you have two unrelated relationships giving you a cross join effect|||I am not getting an error, it is just pulling duplicate fields.
In the Participant table, ParticipantID is a Primary Key and in PartRace and PartReg tables it is a Foreign Key. In the RaceData table, RaceID is a Primary Key and in PartRace and PartReg it is a Foreign Key.
Hope this helps~|||Assuming your data is clean, I believe your selection list is misleading.
You are not pulling any colums of "rd" (RaceData) table.
Perhaps there is a 1-n realtion (participants has entered in more than one races ?). Selelect the PK of RaceData along, this may shed some light.
SELECT
p.ParticipantID,
pr.RaceID,
p.FirstName,
p.LastName,
pr.Bib,
p.Gender,
pr.Age,
pr.AgeGrp,
p.DOB,
p.Address,
p.City,
p.St,
p.Zip,
pr.Clyde,
pr.WhlChr,
pr.RcWlk,
p.Phone,
p.Email,
reg.ShrtSize,
reg.ShrtStyle,
reg.WhereReg,
reg.DateReg,
reg.AmtPd,
rd.RaceID
FROM Participant p
INNER JOIN
PartReg reg
ON p.ParticipantID = reg.ParticipantID
JOIN
PartRace pr
ON pr.ParticipantID = p.ParticipantID
JOIN
RaceData rd
ON pr.RaceID = rd.RaceID
WHERE
(
rd.EventID = 45
AND pr.RaceID = reg.RaceID
)
ORDER BY p.LastName|||Addendum: what is the relation/structure concerning "Eventid" / "RaceId" in RaceData ? does one RACE cover multiple EVENTS ?
You are joining via "EventId", but say PK is "RaceID" ?
...not seeing clear yet...|||Each event has one or more races. The EventID in Events is a primary key and in RaceData is a foreign key. The problem is that some people could have registered for multiple races hence they appear more than once in the PartRace and PartReg tables but only once in the Participant table. I am trying to identify all participants for all of the races in a specific event.|||I found the problem. It was an error in my code that was over-writing the raceid field with the most current one. The query was doing exactly what it was supposed to be doing. The data was corrupted but I have resolved it.
THANKS A TON FOR ALL OF YOUR HELP!
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment