CODEWITHSUNDEEP
sql-serverdatabasejoin
Fhd.ashraf
Fhd.ashraf

Reputation: 537

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

I have tried this but I am not getting corrent counts.

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

Upvotes: 0

Views: 93

Answers (5)

HLGEM
HLGEM

Reputation: 96600

try

SELECT Match.Matchid
    , COUNT(hscore.MatchId) AS HomeScore
    , COUNT(ascore.MatchId) AS AwayScore 
FROM Match  
LEFT OUTER JOIN Score AS hscore 
         ON Match.HomeTeamId = hscore.TeamId AND Match.Matchid = hscore.Matchid
LEFT OUTER JOIN Score AS ascore 
         ON Match.AwayTeamId = ascore.TeamId AND Match.Matchid = ascore.Matchid
GROUP BY  Match.Matchid

If you want to add in the team names you will need to join to the team table twice as well but that can be an inner join.

Upvotes: 1

reaanb
reaanb

Reputation: 10064

Here's my version:

WITH Scores (MatchId, TeamId, cnt) AS (
  SELECT MatchId, TeamId, COUNT(*)
  FROM Score
  GROUP BY MatchId, TeamId
)
SELECT Match.Matchid,
       COALESCE(h.cnt, 0) AS HomeScore,
       COALESCE(a.cnt, 0) AS AwayScore 
FROM Match  
LEFT OUTER JOIN Scores h ON Match.MatchId = h.MatchId AND Match.HomeTeamId = h.TeamId
LEFT OUTER JOIN Scores a ON Match.MatchId = a.MatchId AND Match.AwayTeamId = a.TeamId

SQL Fiddle

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

SELECT
     m.MatchID
    ,ht.Team HomeTeam
    ,htscore.score HomeTeamScore
    ,at.Team AwayTeam
    ,atscore.score AwayTeamScore
    ,m.MatchStartDate MatchDate
FROM Match m
INNER JOIN Team ht ON m.HomeTeamID=ht.TeamID
INNER JOIN Team at ON m.AwayTeamID=at.TeamID
INNER JOIN (
    SELECT MatchID,TeamID,COUNT(*) score
    FROM Score
    GROUP BY MatchID,TeamID
) htscore ON htscore.MatchID=m.MatchID AND htscore.TeamID=ht.TeamID
INNER JOIN (
    SELECT MatchID,TeamID,COUNT(*) score
    FROM Score
    GROUP BY MatchID,TeamID
) atscore ON atscore.MatchID=m.MatchID AND atscore.TeamID=at.TeamID

Upvotes: 0

XtremeBytes
XtremeBytes

Reputation: 1497

This should give what you want

Select MatchID, H.Team as HomeTeam, HomeTeamScore = (Select Count(*) From Score Where MatchID = M.MatchID And TeamID = M.HomeTeamId), A.Team as AwayTeam, AwayTeamScore = (Select Count(*) From Score Where MatchID = M.MatchID And TeamID = M.AwayTeamId), MatchStartDate From Match M Inner Join Team H On M.HomeTeamId = H.TeamID Inner Join Team A On M.AwayTeamId = A.TeamID

Upvotes: 1

JamieD77
JamieD77

Reputation: 13949

You can use Cross Apply to act like a function to get the Team Names and just sub select your scores..

SELECT  m.MatchId,
        ht.Team AS HomeTeam,
        (SELECT COUNT(*) 
         FROM Score s 
         WHERE s.MatchId = m.MatchId AND s.TeamId = ht.TeamId) AS HomeTeamScore,
        at.Team AS AwayTeam,
        (SELECT COUNT(*) 
         FROM Score s 
         WHERE s.MatchId = m.MatchId AND s.TeamId = at.TeamId) AS AwayTeamScore,
        m.MatchStartDate
FROM    Match m
        CROSS APPLY (SELECT TeamID, Team FROM Team WHERE TeamId = m.HomeTeamId) ht
        CROSS APPLY (SELECT TeamID, Team FROM Team WHERE TeamId = m.AwayTeamId) at

SQL Fiddle

Upvotes: 0

Related Questions