title: Query help? MySQL, LEFT JOIN, GROUP BY, missing data
body: <p>How do I change this query so that it still shows 0 flotsam received on 2011-01-01 after the 2011-01-02 row is added?</p>
  
  <pre><code>mysql&gt; SELECT * FROM Items;
  +---------+
  | Name    |
  +---------+
  | widgets | 
  | things  | 
  | stuff   | 
  | flotsam | 
  | jetsam  | 
  +---------+
  
  mysql&gt; SELECT * FROM Received;
  +---------+---------------+-------------+----------+
  | Name    | invoiceNumber | invoiceDate | Quantity |
  +---------+---------------+-------------+----------+
  | widgets |             1 | 2011-01-01  |        1 | 
  | widgets |             2 | 2011-01-01  |        2 | 
  | things  |             3 | 2011-01-01  |        3 | 
  | things  |             4 | 2011-01-01  |        4 | 
  | stuff   |             5 | 2011-01-01  |        5 | 
  | jetsam  |             7 | 2011-01-01  |        7 | 
  +---------+---------------+-------------+----------+
  
  mysql&gt; SELECT Name, IFNULL(SUM(Quantity),0)
      -&gt; FROM Items AS I
      -&gt; LEFT JOIN Received AS R
      -&gt; USING (Name)
      -&gt; WHERE invoiceDate='2011-01-01' OR invoiceDate IS NULL
      -&gt; GROUP BY Name;
  +---------+-------------------------+
  | Name    | IFNULL(SUM(Quantity),0) |
  +---------+-------------------------+
  | flotsam |                       0 | 
  | jetsam  |                       7 | 
  | stuff   |                       5 | 
  | things  |                       7 | 
  | widgets |                       3 | 
  +---------+-------------------------+
  
  mysql&gt; INSERT INTO Received VALUES ('flotsam',8,'2011-01-02',8);
  
  mysql&gt; SELECT Name, IFNULL(SUM(Quantity),0)
      -&gt; FROM Items AS I
      -&gt; LEFT JOIN Received AS R
      -&gt; USING (Name)
      -&gt; WHERE invoiceDate='2011-01-01' OR invoiceDate IS NULL
      -&gt; GROUP BY Name;
  +---------+-------------------------+
  | Name    | IFNULL(SUM(Quantity),0) |
  +---------+-------------------------+
  | jetsam  |                       7 | 
  | stuff   |                       5 | 
  | things  |                       7 | 
  | widgets |                       3 | 
  +---------+-------------------------+
  </code></pre>