Results 1 to 11 of 11

Thread: 5 Table Join

  1. #1
    Join Date
    Apr 2003
    Posts
    114

    Unanswered: 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):
    Code:
    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.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    “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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your code looks fine, apart from the gratuitous and obfuscatory use of aliases.
    Code:
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Apr 2003
    Posts
    114
    Thanks to both of you. It is now fixed. The event date is in the events table. How would you use aliases?

    Thanks!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2006
    Location
    Charleston, SC
    Posts
    4
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Mar 2006
    Location
    Charleston, SC
    Posts
    4
    Ugh. Having trkAddress1 and drvrAddress1, for example, also makes it easier for using Crystal Reports.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Apply aliases in the output recordset.

    And nothing makes Crystal Reports easy. It get a double-ugh.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Mar 2006
    Location
    Charleston, SC
    Posts
    4
    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.

Posting Permissions

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