title: Results reduced after adding left join
body: <p>I am working with MYSQL and I am having some troubles getting the expected results I want.</p>
  
  <p>I am working with 4 tables right now...</p>
  
  <pre><code>matchopponents
  --------------
  opp1id
  opp2id 
  matchid
  
  matches
  -------------
  id 
  title
  
  players
  ------------
  id
  playeralias
  
  ratings 
  ------------
  ratingid
  ratingvalue
  matchid
  </code></pre>
  
  <p>I need a query that will select each row from the matchopponents table, distinguish the playeralias based on the opp1id and opp2id, grab the match title from the matches table, and then round the average total ratings for each respected match in the matchopponent table. The query I have so far is getting close to the expected result however it only returns one row from the matchopponents table. Currently I have 2 rows in the matchopponents table:</p>
  
  <p>opp1id opp2id  matchid<br>
  2       3        11
  4       5        12</p>
  
  <p>The following query returns </p>
  
  <p>Matchtitle                 Player1           Player2    Rating 
  MarineKing vs Ryung        MarineKing        Ryung        3</p>
  
  <p>This is great for the first row... (opp1id 2, opp2id 3, matchid 11)</p>
  
  <p>But nothing is being returned for the second row... When I remove the LEFT JOIN ratings,
  Both are being displayed. I have ensured all the proper ID's are in place and correct. 
  :(</p>
  
  <p>Any help on why this is happening would greatly be appreciated :)</p>
  
  <pre><code>SELECT 
  m.title AS "Match Title",
  p1.playeralias AS "Player 1",
  p2.playeralias AS "Player 2",
  ROUND(IFNULL(AVG(r.ratingvalue), 1)) AS "Rating"
  FROM 
  matchopponents AS MO
  JOIN matches AS m ON mo.matchid = m.id
  JOIN players AS p1 ON mo.opp1id = p1.id 
  JOIN players AS p2 ON mo.opp2id = p2.id
  LEFT JOIN ratings r
  ON 
  r.matchid = mo.matchid
  </code></pre>