Hi there,

I'm building a Football/Soccer competition application for my local club and I want to build a league rankings table on the fly and not to put it in the DB.

my matches table has these fields:

home_team | away_team | home_score | away_score | competition_id | season_id | played

I already have part of the SQL code:

    home_team AS team, 
    count(*) played_matches, 
    count(case when home_score > away_score then 1 end) wins, 
    count(case when away_score> home_score then 1 end) losses, 
    count(case when home_score = away_score then 1 end) draws, 
    sum(home_score) goals_scored, 
    sum(away_score) goals_against, 
    sum(home_score) - sum(away_score) goal_diff,
          case when home_score > away_score then 3 else 0 end 
        + case when home_score = away_score then 1 else 0 end
    ) punten
    select home_team, home_score, away_score from matches 
    union all
    select away_team, away_score, home_score from matches
) matches
group by home_team
order by punten desc, goal_diff desc;
This outputs:

team | played_matches | wins | losses | draws | goals_scored | goals_against | goal_diff | points

But now my 4 problems:

- The home and away_team columns are both foreign keys referencing the id on the teams table, so I want to join the team names on the matches so the output displays names instead of the foreign keys.

- Since I have multiple seasons and competitions, I need to filter the matches first on competition and season id. So it only displays matches from season '14-15' and competition '2A' (par example). I can't get the WHERE statement working on this one.

- I have a table with penalty points which also has an season, competition and team id. I want to add the penalty points to the league table so that it get's subtracted form the points column and an extra column that displays them.

- I'd like to add a column with positions that puts 1 to X in a column that displays the rank in the output.

I am by no means an SQL expert, so I can't figure out how to do this.

Thanks in advance!