title: SQL Left Join with Count
body: <p>I'm working on a video game inventory website. Here is a simplified version of my database tables along with some sample data:</p>
  
  <pre><code>CREATE TABLE `platforms` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(16) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
  
  INSERT INTO `platforms` VALUES(1, 'Nintendo');
  INSERT INTO `platforms` VALUES(2, 'Super Nintendo');
  INSERT INTO `platforms` VALUES(3, 'Nintendo 64');
  
  --
  
  CREATE TABLE `games` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `platform_id` int(10) unsigned NOT NULL,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
  
  INSERT INTO `games` VALUES(1, 1, 'Super Mario Bros.');
  INSERT INTO `games` VALUES(2, 1, 'Super Mario Bros. 2');
  INSERT INTO `games` VALUES(3, 2, 'Super Mario World');
  INSERT INTO `games` VALUES(4, 2, 'Super Mario Kart');
  INSERT INTO `games` VALUES(5, 3, 'Super Mario 64');
  INSERT INTO `games` VALUES(6, 3, 'Mario Kart 64');
  
  --
  
  CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `username` varchar(64) NOT NULL,
    `password` varchar(64) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
  
  INSERT INTO `users` VALUES(1, 'john_doe', '$2a$10$cQhc4VAXVMEyC1tA.VRoWunpNVi7392adacT/weVBzu6XGI6.Jx/K');
  INSERT INTO `users` VALUES(2, 'jane_doe', '$2a$10$Ot2BmlT14hKDxHGIV8jBx.lW76HCWdwuOhNGIYrJO5O7BEtDUWLWu');
  
  --
  
  CREATE TABLE `games_users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `game_id` int(10) unsigned NOT NULL,
    `user_id` int(10) unsigned NOT NULL,
    `created` datetime NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
  
  INSERT INTO `games_users` VALUES(1, 1, 1, '2013-04-12 12:18:09');
  INSERT INTO `games_users` VALUES(2, 3, 1, '2013-04-12 12:18:42');
  INSERT INTO `games_users` VALUES(3, 4, 1, '2013-04-12 12:19:13');
  INSERT INTO `games_users` VALUES(4, 2, 2, '2013-04-12 12:19:32');
  </code></pre>
  
  <p>As you can see, john_doe has 1 Nintendo game (Super Mario Bros.), 2 Super Nintendo games (Super Mario World and Super Mario Kart), and 0 Nintendo 64 games. jane_doe has 1 Nintendo game (Super Mario Bros. 2), 0 Super Nintendo games, and 0 Nintendo 64 games.</p>
  
  <p>I want to write a query specific to a user that will list all of the consoles and also list the number of games the user has for each console.</p>
  
  <p>This is what the results would be for john_doe:</p>
  
  <pre><code>platform.id: 1
  platform.name: Nintendo
  game_count: 1
  
  platform.id: 2
  platform.name: Super Nintendo
  game_count: 2
  
  platform.id: 3
  platform.name: Nintendo 64
  game_count: 0
  </code></pre>
  
  <p>This is what the results would be for jane_doe:</p>
  
  <pre><code>platform.id: 1
  platform.name: Nintendo
  game_count: 1
  
  platform.id: 2
  platform.name: Super Nintendo
  game_count: 0
  
  platform.id: 3
  platform.name: Nintendo 64
  game_count: 0
  </code></pre>
  
  <p>How can I do this?</p>