I am making a small soccer prediction website and one functionality I need is to generate a league table from my database that contains past results. My db has 5 columns;

Fixture_ID.

Home_team.

Away_team.

Htgoals( Number of goals scored by home team).

Atgoals( Number of goals scored by away team).

I have two queries that return the league tables for teams only when playing at home and playing away from home


Home-" Select results.Home_team,
SUM(if(results.Htgoals > results.Atgoals,3,0)) AS W,
SUM(IF(results.Htgoals = results.Atgoals,1,0)) AS D,
SUM(IF(results.Htgoals < results.Atgoals,1,0)) AS L
from results
GROUP BY results.Home_team
order by W desc";

Away table-" Select results.Away_team,
SUM(if(results.Atgoals > results.Htgoals,3,0)) AS W,
SUM(IF(results.Atgoals = results.Htgoals,1,0)) AS D,
SUM(IF(results.Atgoals < results.Htgoals,1,0)) AS L
FROM results
GROUP BY results.Away_team
ORDER BY W DESC";



How can I;

1.Do a union to generate an overall league table

2.Rank teams by the number of points rather than number of wins

3. I would like to include a fixtures table in the db, what is the best way of making the db (fixtures and results table) relational (foreign keys)