title: SQL get a result from two tables
body: <p>I have 2 tables: </p>
  
  <pre><code> 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
   ...
  </code></pre>
  
  <p>Attention: the above two tables are long so the result table may show some rows that are not displayed in the two tables.</p>
  
  <p>Need to find every match with the goals scored by each team </p>
  
  <pre><code> 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
  </code></pre>
  
  <p>My solution : </p>
  
  <pre><code>  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 
  </code></pre>
  
  <p>I am not sure my solution is correct but its structure is long and ugly. </p>
  
  <p>Anyone can suggest a better solution ?</p>
  
  <p>is it possible to solve it with CASE WHEN ?</p>