CODEWITHSUNDEEP
mysqlsql
user3448011
user3448011

Reputation: 1599

SQL get a result from two tables

I have 2 tables: 

 table : game

 id     mdate             stadium                 team1     team2
1001    8 June 2012     National Stadium, Warsaw    POL     GRE
1002    8 June 2012     Stadion Miejski (Wroclaw)   RUS     CZE
1003    12 June 2012    Stadion Miejski (Wroclaw)   GRE     CZE
1004    12 June 2012    National Stadium, Warsaw    POL     RUS
....

 table goal 

 matchid    teamid  player              gtime
 1001        POL    Robert Lewandowski      17
 1001        GRE    Dimitris Salpingidis    51
 1002        RUS    Alan Dzagoev            15
 1002        RUS    Roman Pavlyuchenko      82
 ...

Attention: the above two tables are long so the result table may show some rows that are not displayed in the two tables.

Need to find every match with the goals scored by each team 

 mdate         team1    score1  team2   score2
 1 July 2012    ESP        4    ITA     0
 10 June 2012   ESP        1    ITA     1
 10 June 2012   IRL        1    CRO     3

My solution : 

  SELECT a.mdate, team1, score1, team2, score2
  FROM
  (
       SELECT mdate, team1,
               SUM(CASE WHEN teamid = team1 THEN 1 
                   ELSE 0
                   END) AS score1
        FROM game 
        INNER JOIN goal
        ON id = matchid
        GROUP BY mdate, team1  
   ) AS a
  INNER JOIN 
  (
        SELECT mdate, team2,
                SUM(CASE WHEN teamid = team2 THEN 1 
                         ELSE 0
                    END) AS score2
        FROM game 
      INNER JOIN goal
      ON id = matchid
      GROUP BY mdate, team2
  ) AS b
  ON       
  a.mdate = b.mdate
  ORDER   BY a.mdate, team1, team2

I am not sure my solution is correct but its structure is long and ugly.

Anyone can suggest a better solution ?

is it possible to solve it with CASE WHEN ?

Upvotes: 0

Views: 96

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

The simplest method might be correlated subqueries:

select m.mdate, m.team1,
   (select count(*)
    from goal g
    where g.teamid = m.team1 and g.matchid = m.id
   ) as team1_goals,
   m.team2,
   (select count(*)
    from goal g
    where g.teamid = m.team2 and g.matchid = m.id
   ) as team2_goals
from game m
ORDER BY m.mdate, m.team1, m.team2;

One advantage of this approach is that it can take advantage of an index on goals(matchid, teamid).

Upvotes: 1

Daniel Machet
Daniel Machet

Reputation: 630

OK... found the site and got this to work

SELECT g.mdate, g.team1, CASE WHEN g2.score1 IS NULL THEN 0 ELSE g2.score1 END, g.team2, CASE WHEN g3.score2 IS NULL THEN 0 ELSE g3.score2 END 
FROM game g LEFT OUTER JOIN 
    (SELECT matchid, teamid, COUNT(*) score1 from goal group by matchid, teamid) g2 ON g.team1 = g2.teamid AND g.id = g2.matchid
LEFT OUTER JOIN  
    (SELECT matchid, teamid, COUNT(*) score2 from goal group by matchid, teamid) g3 ON g.team2 = g3.teamid AND g.id = g3.matchid 
ORDER BY g.mdate, g.team1, g.team2

wow... mariadb is pretty vicious on case of table names etc.

The CASE is there to deal with the scenario where null is returned

Upvotes: 0

Related Questions