Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Posts
    114

    Unanswered: Query Syntax Help

    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
    --------------------------------------------------------------------------------

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what is the error you are getting?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2003
    Posts
    114
    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~

  5. #5
    Join Date
    Dec 2004
    Location
    Europe
    Posts
    20
    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.

    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,
       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
    Last edited by matt_p; 01-14-05 at 07:28.

  6. #6
    Join Date
    Dec 2004
    Location
    Europe
    Posts
    20
    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...

  7. #7
    Join Date
    Apr 2003
    Posts
    114
    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.

  8. #8
    Join Date
    Apr 2003
    Posts
    114
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •