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:
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!
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:
tblMatchTeams:
match_id
match_date
tblResults:
match_id
team_id
matchTeam_homeaway
tblTeams:
match_id
team_id
result (e.g. Won/Lost)
result_goals_scored
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!
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
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)
I hope I have the syntax etc correct ... without a sample db, I did not test it.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is perfect. Thank you so much for your help!