Select corresponding value from different table

[JamieTrue]

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

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

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

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

[furicane]

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.

[guido2004]

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.

[JamieTrue]

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

[guido2004]

Or you can make one table, Results, and add another column, which indicates the type of result (Qualifying or Race).

[JamieTrue]

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.