SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select corresponding value from different table

    Hi,

    I am a complete newbie to MySQL so please forgive what is probably my sheer ignorance at this stage.

    I have 2 tables..

    Driver
    Code:
    id name r1 r2 r3 r4 r5 r6 r7 r8 r9 r10
    1  John  2  4  4  7  2  1  6  2  9   6
    Points
    Code:
    id pts
    1   10
    2    9
    3    8
    4    7
    5    6
    6    5
    7    4
    8    3
    9    2
    10   1
    I would like to retrieve the points John gets for each of the 10 races he runs. The number in the r1, r2, r3 etc is the position he came in the race and he is awarded the points according to the id in the Points table. i.e. in Race 1 (r1) he came 2nd and so is awarded 9 points, in Race 2 (r2) he came 4th and so is awarded 7 points etc ... so the output would be
    Code:
    Race Points
     1      9
     2      7
     3      7
     4      4
     5      9
     6     10
     7      5
     8      9
     9      2
    10      5
    I'm sure it's got to be quite simple, but I'm having one of those days where I can't get my head around the simplest of things (must be because it's friday). Any pointers would be gratefully received. Thanks

    Jamie

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Before Rudy comes in and solves this I'd just like to ask whether it's likely that your points table will get additional records?
    Because, if you're only going to have 10 races trough the lifetime of your game/application - why even bother with points table?
    If it's likely that you will have more than 10 points, then I'm afraid your drivers table design is not suited for the task.

    Also, the records in your points table seem to be reverse index of what's stored in r1 - r10 columns of driver, you can easily extrapolate what the points are without joining the points table 10 times.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Yes, get rid of those r1..r10 columns. Create a new table, Races, with columns:
    driverid
    raceid
    position

    Then it'll be easy to JOIN the tables together.

  4. #4
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I get you ... thanks.
    I guess I should do the same for Qualifying Position too, so would have

    QualifyingResult
    driverid
    raceid
    position

    RaceResult
    driverid
    raceid
    position

    So points can be awarded for the qualifying position as well

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Or you can make one table, Results, and add another column, which indicates the type of result (Qualifying or Race).

  6. #6
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd just like to say thanks to you guys, you've helped me immensely. It shows that designing a database correctly to begin with can help solve later issues.

    I ended up with a Results table comprising:

    Results
    DriverID
    RaceID
    QualifyingPos
    GridPos
    RacePos

    which allows me to join tables to retrieve points awarded for their positions for each race easily.

    Thanks also for the swift replies ... very much appreciated.


Bookmarks

Posting Permissions

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