I'm in the process of designing a database to keep track of MLS standings, and I'm having trouble working out how to calculate the league table standings.
At the moment I have the following relevant tables:
result (e.g. Won/Lost)
So, for each fixture I'll have 2 entries in tblFixtures, and when a match is finished I'll add 2 rows to tblResults, both with the same fixture_id but different team_ids, to reflect the result of each team.
At the moment I'm very lost in my attempt to create an SQL query that would list all of the teams in the conference, their games played, goals scored and conceded, and points gained (3 points for a win). Would anyone possibly be able to give me some advice to set me on the right track?
Thanks very much in advance!