Link to home
Start Free TrialLog in
Avatar of tcra
tcra

asked on

SQL Query for MLS League Standings Table

Hi guys,

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:

tblMatches:

match_id
match_date
tblMatchTeams:

match_id
team_id
matchTeam_homeaway
tblResults:

match_id
team_id
result (e.g. Won/Lost)
result_goals_scored
tblTeams:

team_id
team_name

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!
Avatar of als315
als315
Flag of Russian Federation image

Can you upload your DB with some sample data and show expected result from these data?
Your question show the structure tables tblMatches but the description talks about tblFixtures ... are these the same table??
You mention "all of the teams in the conference" ... but none of the table have a column that would indicate conference.

Using the table names provided ... this might be a start, try this a base query
SELECT ma.match_id, ma.match_date
     , hm.team_id as home_team, hr.result as home_result, hr.result_goals_scored as home_score
     , ay.team_id as away_team, ar.result as away_result, ar.result_goals_scored as away_score
FROM tblMatches ma
INNER JOIN (tblMatchTeams hm ON ma.match_id = hm.match_id and hm.matchTeam_homeaway = 'Home'
 LEFT JOIN tblResults hr ON hm.match_id = hr.match_id and hm.team_id = hr.team_id)
INNER JOIN (tblMatchTeams ay ON ma.match_id = ay.match_id and ay.matchTeam_homeaway = 'Away'
 LEFT JOIN tblResults ar ON hm.match_id = ar.match_id and hm.team_id = ar.team_id)

Open in new window

I hope I have the syntax etc correct ... without a sample db, I did not test it.
Avatar of tcra
tcra

ASKER

Thanks very much for your input, lwadwell.  Apologies for the errors in my original post.  Anywhere I mention Fixtures is supposed to read Matches.  For now I'm just doing teams in the same conference, so we don't have to distinguish between those in the query.

I've attached a sample database file now.  Does this work?

I'd expect the standings to look something like this from the sample data:

[Team Name] [Matches Played] [Matches Won] [Matches Drawn] [Matches Lost] [Goals Scored] [Goals Conceded] [Goal Difference] [Total Points]
Colorado Rapids 2 1 1 0 2 0 2 4
D.C. United 1 1 0 0 1 0 1 3
Chivas USA 1 0 1 0 1 1 0 1
Columbus Crew 1 0 1 0 0 0 0 1
Chicago Fire 2 0 1 1 1 3 -2 1
FC Dallas 1 0 0 1 0 1 -1 0

So that table is sorted first by total points then by goal difference then by goals scored.

I tried pasting your suggested query as-is into Microsoft Access and got a syntax error.  Perhaps that has something to do with an intricacy on Microsoft Access?  Is there also a way to work out points gained in total by taking 3 points for a loss and 1 point for a draw?

Thanks very much!!
Sample-Database.accdb
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tcra

ASKER

That is perfect.  Thank you so much for your help!