Difficult mysql league table question

I am using this MySql query for creating a league table and it works perfectly. The problem I have is that this query works for only one season. When a new season begins I can't enter a new fixture without it affecting the previous seasons table.

I have a seasons table which contains: seasonID and seasonName I have a fixtures table which contains: matchID, seasonID, matchDate, matchHomeID, matchAwayID, matchHomeGoals, matchAwayGoals, matchAttendance* I have a teams table which contains: teamID, team, stadium**

*The matchAttendance field is not a part of this query. **The stadium field is not a part of this query.

So basically I would like to be able to select games that have taken place in a particular season.

The table on my site can be seen here

As you can see I have a drop-down box which contains three season names. Obviously it doesn't work but it gives you an idea as to what I am trying to do.

Here is the query.

SELECT team开发者_如何学编程 AS Teams, Sum(P) AS P,Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
  SUM(WA) AS WA, SUM(DA) AS DA, SUM(LA) AS LA, SUM(F) as F,SUM(A) AS A,
  SUM(FA) AS FA, SUM(AA) AS AA, SUM(GD) AS GD, SUM(Pts) AS Pts
FROM(
  SELECT 
    matchHomeID Teams, 
    1 P,
    IF(matchHomeGoals > matchAwayGoals,1,0) W,
    IF(matchHomeGoals = matchAwayGoals,1,0) D,
    IF(matchHomeGoals < matchAwayGoals,1,0) L,
    IF(matchHomeGoals > matchAwayGoals,0,0) WA,
    IF(matchHomeGoals = matchAwayGoals,0,0) DA,
    IF(matchHomeGoals < matchAwayGoals,0,0) LA,
    matchHomeGoals F,
    matchAwayGoals A,
    0 FA,
    0 AA,
    matchHomeGoals-matchAwayGoals GD,
    CASE WHEN matchHomeGoals > matchAwayGoals THEN 3 WHEN matchHomeGoals = matchAwayGoals THEN 1 ELSE 0 END PTS
  FROM au_fixtures
  UNION ALL
  SELECT
    matchAwayID,
    1 P,
    IF(matchHomeGoals < matchAwayGoals,0,0),
    IF(matchHomeGoals = matchAwayGoals,0,0),
    IF(matchHomeGoals > matchAwayGoals,0,0),
    IF(matchHomeGoals < matchAwayGoals,1,0) WA,
    IF(matchHomeGoals = matchAwayGoals,1,0) DA,
    IF(matchHomeGoals > matchAwayGoals,1,0) LA,
    0,
    0,
    matchAwayGoals FA,
    matchHomeGoals AA,
    matchAwayGoals-matchHomeGoals GD,
    CASE WHEN matchHomeGoals < matchAwayGoals THEN 3 WHEN matchHomeGoals = matchAwayGoals THEN 1 ELSE 0 END
    FROM au_fixtures
) as tot
JOIN au_teams t ON tot.Teams=t.teamID

GROUP BY Teams
ORDER BY SUM(Pts) DESC

you need to include SeasonId in your query so Where SeasonId=season_you_want somewhere in the query, probably before the Group By after scanning the query.

After re-reading you probably want to do the where on the season name in the seasons table and do a join to the seasons table.