title: Mysql query with Union All and Join for League table
body: <p>for a soccer league i have two tables: </p>
  
  <ul>
  <li>"teams" is a table that lists all teams as unique entries ('id',
  'team_name')</li>
  <li>"matches" is a table that shows the match results: In the columns
  'home' and 'away' i save each game result. 'home_team_id' and
  'away_team_id' is the association to the teams-table.</li>
  </ul>
  
  <p>This is how my query looks like:</p>
  
  <pre><code>SELECT Teams, Sum(P) as 'Matches', Sum(W) as 'win', Sum(D) as 'draw', Sum(L) as 'lost',
         SUM(Pts) as 'points'
    FROM (
          SELECT home Teams, 1 P,
                  IF (home &gt; away,1,0) W,
                  IF (home = away,1,0) D,
                  IF (home &lt; away,1,0) L,
                  CASE 
                      WHEN home &gt; away THEN 3
                      WHEN home = away THEN 1 
                      ELSE 0
                  END PTS
            FROM `matches`
  
          UNION ALL
          SELECT away Teams, 1,
                  IF (home &lt; away,1,0),
                  IF (home = away,1,0),
                  IF (home &gt; away,1,0),
                  CASE 
                      WHEN home &lt; away THEN 3
                      WHEN home = away THEN 1
                      ELSE 0
                  END
            FROM `matches`
          ) AS ERG
  GROUP BY Teams
  ORDER BY SUM(Pts) DESC 
  </code></pre>
  
  <p>Now i want the team names (teams.team_name) from the team-table. To achieve this i tried several join-statements with no luck. </p>
  
  <p>It's obvious that the teams-table can contain teams who did not attend a match. These teams need to be displayed with zero-results. </p>
  
  <p>Therefore I tried a LEFT JOIN:</p>
  
  <pre><code>SELECT team_name AS 'Teams'
    FROM `teams`
    LEFT JOIN matches ON ( teams.id = matches.home_team_id )
  </code></pre>
  
  <p>right after the ORDER-line in the end. I got an error message.
  I use MySQL 5.1.44, so nested selects shouldn't be a problem.</p>
  
  <p>Any idea? </p>