• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1155
  • Last Modified:

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!
0
tcra
Asked:
tcra
  • 2
  • 2
1 Solution
 
als315Commented:
Can you upload your DB with some sample data and show expected result from these data?
0
 
lwadwellCommented:
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.
0
 
tcraAuthor Commented:
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
0
 
lwadwellCommented:
Try
select tm.team_name as [Team Name], count(*) as [Matched Played]
     , SUM(iif(rs.result = 'Win',1,0)) as [Matches Won]
     , SUM(iif(rs.result = 'Draw',1,0)) as [Matches Drawn]
     , SUM(iif(rs.result = 'Lost',1,0)) as [Matches Lost]
     , SUM(rs.result_goals_scored) as [Goals Scored]
     , SUM(ot.result_goals_scored) as [Goals Conceded]
     , SUM(rs.result_goals_scored)
       - SUM(ot.result_goals_scored) as [Goals Difference]
     , SUM(iif(rs.result = 'Win',3,iif(rs.result = 'Draw',1,0))) as [Total Points]
from (tblTeams tm
inner join tblResults rs on tm.team_id = rs.team_id)
inner join tblResults ot on rs.match_id = ot.match_id and rs.team_id <> ot.team_id 
group by tm.team_name

Open in new window

The sorting I did not implement ... use this as a saved query and sort the results in another query is neater.

For the record, my original SQL, now working, is:
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)
 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)
 LEFT JOIN tblResults ar ON ay.match_id = ar.match_id and ay.team_id = ar.team_id)
WHERE hm.matchTeam_homeaway = 'Home'
and ay.matchTeam_homeaway = 'Away'

Open in new window

0
 
tcraAuthor Commented:
That is perfect.  Thank you so much for your help!
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now