asked
Joining 3 tables to count the score of a Match.
I have 3 tables. Team TeamId Team 1 Manchester United 2 Arsenal 3 Liverpool ------------------ Match MatchId HomeTeamId AwayTeamId MatchStartDate 3 1 2 2/2/2015 8 3 1 6/2/2015 ------------------ Score ScoreId MatchId TeamId ScorTime 1 3 1 2/2/2015 12:30:00 2 3 2 2/2/2015 12:35:00 3 3 1 2/2/2015 12:38:00 4 8 1 6/2/2015 12:45:00 5 8 1 6/2/2015 12:49:00 I wanted something like MatchId HomeTeam HomeTeamScore AwayTeam AwayTeamScore MatchDate 3 Manchester United 2 Arsenal 1 2/2/2015 8 Liverpool 0 Manchester United 2 6/2/2015 thank you.
Seems like homework - we are happy to help you get the right answer, but would rather you learn along the way. What have you tried so far? Start with the Match table and use joins to get the extra details. I presume you have missed off a Score column from the Score table?
haha.. not really . tried and failed hence posted it here.. and there is no Score column coz I am suing a count.. something like this. SELECT COUNT(hscore.MatchId) AS HomeScore, COUNT(ascore.MatchId) AS AwayScore FROM Match right OUTER JOIN Score AS hscore ON Match.AwayTeamId = hscore.TeamId LEFT OUTER JOIN Score AS ascore ON Match.HomeTeamId = ascore.TeamId
Here's a convoluted way of doing this. Start with the Match table, as that is the base for your query, then we need to join to the Team table twice to get the team names select Match.MatchId, HomeTeam.Team, AwayTeam.Team, Match.MatchStartDate as MatchDate from @Match Match join @Team HomeTeam on HomeTeam.TeamId = Match.HomeTeamId join @Team AwayTeam on AwayTeam.TeamId = Match.AwayTeamId gives MatchId Team Team MatchDate ----------- ---------------------- --------------------- ---------- 3 Manchester United Arsenal 2015-02-02 8 Liverpool Manchester United 2015-02-06 Using a query like select MatchId, TeamId, count(*) as Score from @Score group by MatchId, TeamId we can get the scores per team per match MatchId TeamId Score ----------- ----------- ----------- 3 1 2 8 1 2 3 2 1 but notice that we don't get any rows where the score is 0! We can then use this query in line (twice) to get both the home and away scores select Match.MatchId, HomeTeam.Team, isnull(HomeScores.Score,0) as HomeTeamScore, AwayTeam.Team, isnull(AwayScores.Score,0) as AwayTeamScore, Match.MatchStartDate as MatchDate from @Match Match join @Team HomeTeam on HomeTeam.TeamId = Match.HomeTeamId join @Team AwayTeam on AwayTeam.TeamId = Match.AwayTeamId left join ( select MatchId, TeamId, count(*) as Score from @Score group by MatchId, TeamId ) HomeScores on HomeScores.MatchId = Match.MatchId and HomeScores.TeamId = HomeTeam.TeamId left join ( select MatchId, TeamId, count(*) as Score from @Score group by MatchId, TeamId ) AwayScores on AwayScores.MatchId = Match.MatchId and AwayScores.TeamId = AwayTeam.TeamId Notice how we have to use an outer join `LEFT JOIN` and use `isnull()` function to check where a score is 0 - i.e. where there is no score data MatchId Team HomeTeamScore Team AwayTeamScore MatchDate ----------- ------------------- ------------- -------------------- ------------- ---------- 3 Manchester United 2 Arsenal 1 2015-02-02 8 Liverpool 0 Manchester United 2 2015-02-06 As an exercise I'll leave it to you to remove the duplication of calculating the scores - hint try using a CTE
16 People are following this question.
