title: MySQL LEFT JOIN SELECT not selecting all the left side records?
body: <p>I'm getting odd results from a <code>MySQL SELECT</code> query involving a <code>LEFT JOIN</code>, and I can't understand whether my understanding of <code>LEFT JOIN</code> is wrong or whether I'm seeing a genuinely odd behavior.</p>
  
  <p>I have a two tables with a many-to-one relationship: For every record in <code>table 1</code> there are 0 or more records in <code>table 2</code>. I want to select all the records in table 1 with a column that counts the number of related records in table 2. As I understand it, <code>LEFT JOIN</code> should always return all records on the <code>LEFT</code> side of the statement.</p>
  
  <p>Here's a test database that exhibits the problem:</p>
  
  <pre><code>CREATE DATABASE Test;
  USE Test;
  
  CREATE TABLE Dates (
     dateID INT UNSIGNED NOT NULL AUTO_INCREMENT,
     date DATE NOT NULL,
     UNIQUE KEY (dateID)
  ) TYPE=MyISAM;
  
  
  CREATE TABLE Slots (
     slotID INT UNSIGNED NOT NULL AUTO_INCREMENT,
     dateID INT UNSIGNED NOT NULL,
     UNIQUE KEY (slotID)
  ) TYPE=MyISAM;
  
  INSERT INTO Dates (date) VALUES ('2008-10-12'),('2008-10-13'),('2008-10-14');
  INSERT INTO Slots (dateID) VALUES (3);
  </code></pre>
  
  <p>The Dates table has three records, and the Slots 1 - and that record points to the third record in Dates.</p>
  
  <p>If I do the following query..</p>
  
  <pre><code>SELECT d.date, count(s.slotID) FROM Dates AS d LEFT JOIN Slots AS s ON s.dateID=d.dateID GROUP BY s.dateID;
  </code></pre>
  
  <p>..I expect to see a table with 3 rows in - two with a count of 0, and one with a count of 1. But what I actually see is this:</p>
  
  <pre><code>+------------+-----------------+
  | date       | count(s.slotID) |
  +------------+-----------------+
  | 2008-10-12 |               0 |
  | 2008-10-14 |               1 |
  +------------+-----------------+
  </code></pre>
  
  <p>The first record with a zero count appears, but the later record with a zero count is ignored. </p>
  
  <p>Am I doing something wrong, or do I just not understand what LEFT JOIN is supposed to do?</p>