MySQL LEFT JOIN SELECT not selecting all the left side records?

Question

I'm getting odd results from a MySQL SELECT query involving a LEFT JOIN, and I can't understand whether my understanding of LEFT JOIN is wrong or whether I'm seeing a genuinely odd behaviour.

I have a two tables with a many-to-one relationship: For every record in table 1 there are 0 or more records in table 2. 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, LEFT JOIN should always return all records on the LEFT side of the statement.

Here's a test database that exhibits the problem:

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);

The Dates table has three records, and the Slots 1 - and that record points to the third record in Dates.

If I do the following query..

SELECT d.date, count(s.slotID) FROM Dates AS d LEFT JOIN Slots AS s ON s.dateID=d.dateID GROUP BY s.dateID;

..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:

+------------+-----------------+
| date       | count(s.slotID) |
+------------+-----------------+
| 2008-10-12 |               0 |
| 2008-10-14 |               1 |
+------------+-----------------+

The first record with a zero count appears, but the later record with a zero count is ignored.

Am I doing something wrong, or do I just not understand what LEFT JOIN is supposed to do?

Answer 1

I think you mean to group by d.dateId.

Answer 2

Try removing the GROUP BY s.dateID

Answer 3

You need to GROUP BY d.dateID. In two of your cases, s.DateID is NULL (LEFT JOIN) and these are combined together.

I think you will also find that this is invalid (ANSI) SQL, because d.date is not part of a GROUP BY or the result of an aggregate operation, and should not be able to be SELECTed.

Answer 4

The dateid for 10-12 and 10-13 are groupd together by you. Since they are 2 null values the count is evaluated to 0

Answer 5

replace GROUP BY s.dateID with d.dateID.

Answer 6

I don't know if this is valid in MySQL but you could probably void this mistake in the future by using the following syntax instead

SELECT date, count(slotID) as slotCount
FROM Dates LEFT OUTER JOIN Slots USING (dateID)
GROUP BY (date)

By using the USING clause you don't get two dateID's to keep track of.