question

fashraf avatar image
fashraf asked

Join three tables for different Count.

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 ![alt text][1] Ive tried this but I fail to get it. SELECT Team.TeamName,COUNT(h.HomeTeamId )as TotalMatch,sum(case when h.Match_Status= Team.TeamId then 1 else 0 end) as HomeScore,sum(case when h.Match_WonBy= Team.TeamId or a.Match_Status= Team.TeamId then 1 else 0 end) as Total FROM Team right JOIN Match h ON Team.TeamId = h.HomeTeamId join Match a on Team.TeamId=a.AwayTeamId group by Team.TeamName [1]: /storage/temp/2717-capture.png FYI: its not a homework.
joinscountmssqljoin
capture.png (67.5 KiB)
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Did the answer in your earlier question not help? https://ask.sqlservercentral.com/questions/122607/joining-3-tables-to-count-the-score-of-a-match.html
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Closing as duplicate
0 Likes 0 ·
fashraf avatar image fashraf commented ·
No Sir, Since the earlier one was Score. this is the aggregates and ranking teams on the basis of count.
0 Likes 0 ·

0 Answers