How to combine multiple historic tables to get an "all time" stats list?

Databases
1

Hi All,

Basically I run a small website for a soccer league. The league has been going 5 or 6 years and each year I create a new set of tables. So I have a table called stats2008,stats2009,stats2010, etc. I have a SQL statement that looks at the stats table and calculates the goals and assists and spits out a leaderboard for the season. However, I would also like to be able to have a statement that can do the same thing but calculate using each seasons stats to get an all-time leaderboard using the tables from previous seasons? Looking back in an ideal world I probably should have just used one table for this and had a column that specified the current season however I only do this on the side for fun so I guess I didint have that longterm visison.

Is this possible? Or would I need to redesign the tables? My SQL statement for the current season stats is below (the $variable would specify the current year via code):


SELECT p.id
    , p.player
    , p.team
    , p.mugshot
    , coalesce(sum(s.goals),0) as goals
    , coalesce(sum(s.assists),0) as assists
    , coalesce(sum(s.fouls),0) as fouls
    , coalesce(sum(s.assists + s.goals),0) as points
    , t.teamname
FROM $league as p
LEFT OUTER JOIN (SELECT goals
        , assists
        , fouls
        , player_id
    FROM $stats) as s
    on s.player_id = p.id
LEFT OUTER JOIN (SELECT id
       ,teamname
     FROM $teams) as t
     on t.id = p.team
GROUP
    BY p.id
    , p.player
ORDER BY assists DESC
LIMIT 0,10
2

if you want overall stats for multiple tables, use a UNION query instead of FROM $stats

e.g.

  FROM $league as p
LEFT OUTER 
  JOIN ( SELECT goals
              , assists
              , fouls
              , player_id 
           FROM stats2008
         UNION ALL
         SELECT goals
              , assists
              , fouls
              , player_id 
           FROM stats2009
         UNION ALL
         SELECT goals
              , assists
              , fouls
              , player_id 
           FROM stats2010  ) as s
3

Thanks. I figured this out searching through another thread. I had tried with UNION but UNION ALL gives me what I need.

This may be complicating things a bit, but is there anyway I can count the number of teams the particular player has played for? In the first statement I Left Outer Join the teams table but this would only be for a specific year. It would be great in the results if I could also return a number of number of teams, this would essentially give me the number of seasons played

4

if you examine your query results, i think you’ll find that any player who has played for more than one team, either in your original query looking at just one year, or the query with the UNION, will have incorrect results, because of the “cross join effects” of each of the player’s teams being matched with each row of stats for that player

my advice is, remove the join to teams altogether (in both versions of the query) and check your results

if you want number of a player’s teams for your leaderboard, i can help you add that after you have completely verified the accuracy of the results without it

5

You were right. I removed teams all together. I just discovered an error though. While the results are correct, it only displays those players who are playing in the current season as I am doing the initial select statement on the current years “league” table which contains all the players names and mugshots. I am thinking it is best to just load all players into this years table and keep doing that each year?

I currently have


SELECT p.id
    , p.player
    , p.mugshot
    , coalesce(sum(s.goals),0) as goals
    , coalesce(sum(s.assists),0) as assists
    , coalesce(sum(s.fouls),0) as fouls
    , coalesce(sum(s.assists + s.goals),0) as points
FROM league2014 as p
LEFT OUTER JOIN (SELECT goals
        , assists
        , fouls
        , player_id
    FROM stats2014
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
    FROM stats2013
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
    FROM stats2012
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
    FROM stats2011
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
    FROM stats2010
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
    FROM stats2009
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
    FROM stats2008) as s
    on s.player_id = p.id
GROUP
    BY p.id
    , p.player
ORDER BY points DESC
LIMIT 0,10
6

Ok. So last question. I combined all the players to one table and am now trying to compile records, such as most points in a game, most goals in a game, most assists in a game over all of the season. I have the SQL results I am looking for, the only thing I would like added are the team names. I have succesfully pulled the teamid and the opponent_id, now I just need to look these up in the teams table. Is there anyway I can do both lookups in this statement, or would that be too much and I have to split it up?


SELECT p.id
    , p.player
    , p.mugshot
, s.team_id
, s.opponent_id
    , s.goals
    , s.assists
    , s.fouls
    , coalesce(s.assists + s.goals) as points
FROM league2014 as p
LEFT OUTER JOIN (SELECT goals
        , assists
        , fouls
        , player_id
        , team_id
        , opponent_id
    FROM stats2014
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
        , team_id
        , opponent_id
    FROM stats2013
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
        , team_id
        , opponent_id
    FROM stats2012
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
        , team_id
        , opponent_id
    FROM stats2011
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
        , team_id
        , opponent_id
    FROM stats2010
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
        , team_id
        , opponent_id
    FROM stats2009
 UNION ALL
 SELECT goals
        , assists
        , fouls
        , player_id
        , team_id
        , opponent_id
    FROM stats2008) as s
    on s.player_id = p.id
ORDER BY points DESC
LIMIT 0,10
7

yes, that’s fine

you can have multiple many-to-one relationships in a query with no problem

each id (teamid and the opponent_id) can join to its respective lookup table, as it will only return one row into the join

if both of them are joining to the teams table, you still need two joins, using table aliases to distinguish the “copies” of the teams table