title: MySQL Right Outer Join Null Value, Standings(Rankings) With All Users
body: <p>I'm trying to create a standings(rankings) query.</p>
  
  <p>I use tables <code>users</code>, <code>schedule</code> and <code>picks</code> to calculate the standings. However the flaw with this is if a user has not submitted any picks that user will not show up in the standings.</p>
  
  <p>The query below returns all users that have submitted picks. User 2 and user 4 have not submitted picks and there for does not show up.</p>
  
  <pre><code>+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  | userID | userName | totalWins | totalLost | totalPushs | totalPoints | totalBets | trueBets |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   1    |   aaaa   |     0     |     0     |     1      |     0.5     |     1     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   3    |   cccc   |     0     |     0     |     1      |     0.5     |     1     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   5    |   eeee   |     0     |     0     |     1      |     0.5     |     1     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  </code></pre>
  
  <p>I'm trying to create it where all users will show up in the standings regardless if they have submitted a pick or not. Like below.</p>
  
  <pre><code>+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  | userID | userName | totalWins | totalLost | totalPushs | totalPoints | totalBets | trueBets |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   1    |   aaaa   |     0     |     0     |     1      |     0.5     |     1     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   2    |   bbbb   |     0     |     0     |     0      |      0      |     0     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   3    |   cccc   |     0     |     0     |     1      |     0.5     |     1     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   4    |   dddd   |     0     |     0     |     0      |      0      |     0     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   5    |   eeee   |     0     |     0     |     1      |     0.5     |     1     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  </code></pre>
  
  <p>However, I'm getting this returned</p>
  
  <pre><code>+--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  | userID | userName | totalWins | totalLost | totalPushs | totalPoints | totalBets | trueBets |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   1    |   aaaa   |     0     |     0     |     1      |     0.5     |     1     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |  NULL  |   NULL   |     0     |     0     |     0      |      0      |     0     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   3    |   cccc   |     0     |     0     |     1      |     0.5     |     1     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |  NULL  |   NULL   |     0     |     0     |     0      |      0      |     0     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  |   5    |   eeee   |     0     |     0     |     1      |     0.5     |     1     |    0     |
  +--------+----------+-----------+-----------+------------+-------------+-----------+----------+
  </code></pre>
  
  <p>I have gotten to the point where I am trying to RIGHT OUTER JOIN <code>users</code> table where all the users resides to the query but NULL values are showing as expected because of no matches.</p>
  
  <p>How would I be able to replace those NULL values with the correct <code>userID</code> and <code>userName</code></p>
  
  <p>This is my code</p>
  
  <pre><code>SELECT
      lt.userID,
      lt.userName,
      IFNULL(lt.totalWins,0) AS totalWins,
      IFNULL(lt.totalLost,0) AS totalLost,
      IFNULL(lt.totalPushs,0) AS totalPushs,
      IFNULL((totalWins+(totalPushs*.5)),0) AS totalPoints,
      IFNULL(totalWins+totalLost+totalPushs,0) AS totalBets,
      IFNULL(totalWins+totalLost,0) AS trueBets
  FROM (
      SELECT
          userID,
          userName,
          SUM(win) AS totalWins,
          SUM(lost) AS totalLost,
          SUM(push) AS totalPushs
      FROM (
          SELECT
              *,
              (finalResult = 'win') AS win,
              (finalResult = 'loss') AS lost,
              (finalResult = 'push') AS push
          FROM (
              SELECT 
                  users.userID,
                  userName,
                  IF (pickID=visitorID, visitorResult, homeResult) AS finalResult
              FROM
                  users
              JOIN
                  picks 
              ON
                  users.userID = picks.userID
              JOIN
                  schedule
              ON
                  picks.gameID = schedule.gameID
              WHERE
                  weekNum &lt;= 1
          ) x
      ) x
      GROUP BY
          userID
  ) lt
  RIGHT OUTER JOIN
      users
  ON
      users.userID = lt.userID
  LIMIT 100
  </code></pre>