Missing SQL entries when combining LEFT/INNER JOIN + GROUP BY

I have the following MySQL structure (minimized a lot):

CREATE TABLE `site_movies` (
  `id` int(10),
  `title` varchar(90),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

INSERT INTO `site_movies` VALUES(1, 'Borrowers, The');
INSERT INTO `site_movies` VALUES(2, 'Louis C.K.: Chewed Up');
INSERT INTO `site_movies` VALUES(3, 'Louis C.K.: Shameless');
INSERT INTO `site_movies` VALUES(4, 'Vinni-Pukh');

CREATE TABLE `site_movies_directors` (
  `id` mediumint(8),
  `name` varchar(255),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `site_movies_directors_connections` (
  `movie_id` mediumint(8),
  `director_id` mediumint(8)
) ENGINE=MyISAM;

CREATE TABLE `site_movies_seen` (
  `object_id` int(10),
  `date` varchar(10),
  `rating` tinyint(2)
) ENGINE=MyISAM;

INSERT INTO `site_movies_seen` VALUES(1, '0', 4);
INSERT INTO `site_movies_seen` VALUES(2, '1293821757', 5);
INSERT INTO `site_movies_seen` VALUES(3, '1293821758'开发者_如何学Go, 7);
INSERT INTO `site_movies_seen` VALUES(4, '0', 6);

And then the following query (also minimized a lot):

SELECT m.title, s.date
FROM site_movies_seen s
INNER JOIN site_movies m ON s.object_id = m.id
LEFT JOIN site_movies_directors_connections AS mdc ON ( m.id = mdc.movie_id ) 
GROUP BY mdc.movie_id, s.date
ORDER BY s.date ASC

Prints:

title   date 
Borrowers, The  0
Louis C.K.: Chewed Up   1293821757
Louis C.K.: Shameless   1293821758

Notice that "Vinni-Pukh" is missing because it is the second entry in the _seen table with date = 0. How can I include all entires, even when several entires have the same timestamp?

Change your group by statement to this:

GROUP BY m.id, s.date

Your join condition says m.id = mdc.movie_id, so you might think that these two fields are always equal and therefore it doesn't matter whether you write m.id or mdc.movie_id in your GROUP BY. This is not true because you are using a LEFT JOIN, not an INNER JOIN. This means that mdc.movie_id can be NULL, and all NULL entries go into the same group.

Also, since you aren't selecting any columns from the site_movies_directors_connections you should omit it from the query completely.

SELECT m.title, s.date
FROM site_movies_seen s
INNER JOIN site_movies m ON s.object_id = m.id
GROUP BY m.id, s.date
ORDER BY s.date