MySQL link field on table to specific field in another table
Posted on 2010-01-05
I have a problem that I am not sure what terminology to use to describe it and fix it, so here goes.
I have a table that gets populated with players scores for a fantasy football contest, there are a couple hundred players and I am already acheving this piece of it.
The table looks like this. Basically we track the score for each player during and after each game played.
player_id - week - year - points
In this contest, I have several thousand teams that pick from the couple hundred players to have on their teams. Right now, I am pulling the scores from the above table when the team visits their page to see how they are doing, it's done individually to keep the processing power down.
The team table looks like the one above, but has the team ID added to it.
team_id - player_id - week - year - points
Is it possible to have the points field in the team table show whatever is in the point field in the players table without loading the info via PHP script or something?
I'd like to be able to pull leaderboards among other stats based on the results of all the teams data, but currently unless they all visit thier pages, this is not possible. I tried a loop grabbing and inserting the data, but with 10K plus teams, it takes a while and I am on a shared host.
Anything I can do to make this more efficient?