title: MySQL Joining two queries for sports team
body: <p>I am trying to display a list of teams with the number of goals they have scored (and order them by greatest to smallest) but am having trouble with joining all the queries together</p>
  
  <p>Table 1: Teams</p>
  
  <pre><code>teamid teamname
    1      team1
    2      team2
    3      team3
  </code></pre>
  
  <p>Table 2: Results</p>
  
  <pre><code> id gameid teamid gf
    1    1     1    5
    2    2     1    3
    3    1     2    0
    4    2     2    2
    5    3     3    0
  </code></pre>
  
  <p>What I'm trying to achieve</p>
  
  <pre><code>  1. Team1 8
    2. Team2 2
    3. Team3 0
  </code></pre>
  
  <p><strong>Get list of all teams</strong></p>
  
  <pre><code>SELECT team.teamid, team.teamname
   FROM teams team
  </code></pre>
  
  <p><strong>Gets sum of goals for 1 team</strong></p>
  
  <pre><code>SELECT COALESCE( SUM( gf ) , 0 ) goalsfor
  FROM results
  WHERE teamid = 1
  </code></pre>
  
  <p><strong>Joining of queries</strong></p>
  
  <pre><code>SELECT team.teamid,
         team.teamname,
         COALESCE(res.gf, 0) goalsfor
  FROM teams team
  LEFT JOIN
    (SELECT COALESCE(SUM(res.gf), 0) goalsfor
     FROM results res
     GROUP BY teamid) res ON team.teamid = res.teamid
  ORDER BY goalsfor DESC
  </code></pre>
  
  <p>Been stuck on joining the queries all day</p>