title: MYSQL LEFT JOIN 3 tables include NULL sets
body: <p>Tried to 'reverse engineer' other SO users questions/answers to my question but couldn't. JOINS still elude me.</p>
  
  <p>I have three tables: race (a list of all races), result (results for each race), driver (driver information). Not all drivers have raced in all races at a specific track.</p>
  
  <p>race table</p>
  
  <pre><code>| race_id   | track_id   | year   | complete |
  +-----------+------------+--------+----------+
  | 1         | 1          | 2006   | 1        |
  | ...       | ...        | ...    | ...      |
  | 21        | 1          | 2007   | 1        |
  | ...       | ...        | ...    | ...      |
  | 135       | 1          | 2008   | 1        |
  | ...       | ...        | ...    | ...      |
  | 215       | 1          | 2009   | 1        |
  | ...       | ...        | ...    | ...      |
  | 292       | 1          | 2010   | 1        |
  | ...       | ...        | ...    |...       |
  | 351       | 1          | 2011   | 0        |
  +-----------+------------+--------+----------+
  </code></pre>
  
  <p>result table</p>
  
  <pre><code>| race_id | driver_id | finish |
  +---------+-----------+--------+
  | 135     | 1         | 15     |
  | ...     | ...       | ...    |
  | 292     | 1         | 6      |
  +---------+-----------+--------+
  </code></pre>
  
  <p>driver table</p>
  
  <pre><code>| driver_id | name       |
  +-----------+------------+
  | 1         | Joe Driver |
  +-----------+------------+
  </code></pre>
  
  <p>I want a result set that shows:</p>
  
  <pre><code>| name       | race_id |  year  | finish |
  +------------+---------+--------+--------+
  | Joe Driver | 21      | 2007   | NULL   |
  | Joe Driver | 135     | 2008   | 15     |
  | Joe Driver | 215     | 2009   | NULL   |
  | Joe Driver | 292     | 2010   | 6      |
  +------------+---------+--------+--------+
  </code></pre>
  
  <p>Joe Driver only raced in 2 of the 4 races that were at track_id = 1, after 2006 and only includes races that are complete (1)</p>
  
  <p>This is my current query:</p>
  
  <pre><code>SELECT driver.driver, race.id, race.year, result.finish
  FROM race
  LEFT JOIN result ON race.id = result.race_id
  LEFT JOIN driver ON result.driver_id = driver.driver_id
  WHERE race.track_id = 1
  AND race.year &gt; 2006
  AND race.complete = 1
  AND driver.driver_id = 1
  ORDER BY race.id ASC
  </code></pre>
  
  <p>The result I get only shows the races the driver was in:</p>
  
  <pre><code>| name       | race_id |  year  | finish |
  +------------+---------+--------+--------+
  | Joe Driver | 135     | 2008   | 15     |
  | Joe Driver | 292     | 2010   | 6      |
  +------------+---------+--------+--------+
  </code></pre>
  
  <p>I'm sure I'm overlooking a simple rule to JOINS?</p>
  
  <p>Thanks!</p>