title: MYSQL Parent -> Child (Comment system with sorting and limiting replies)
body: <p>I have been working on this problem for longer than I care to admit. Any help would be greatly appreciated!</p>
  
  <p>I am working on a basic commenting system with replies (limited to one level).</p>
  
  <p>The model I am using is pretty self explanatory.</p>
  
  <p>As you can see,</p>
  
  <p>when <code>commenterID</code> and <code>userID</code> are equal AND <code>parentID</code> is null, it will be a "status update" and should be featured on main page. This is a parent.</p>
  
  <p><strong>I need to include every replies that have a parent featured on the front page (returning isMain true).</strong></p>
  
  <p>In my example, ID 1 is the parent status update and ID 3,4 are replies to ID 1.</p>
  
  <p>It will be easier to explain my issue by dumping some data:</p>
  
  <pre><code>CREATE TABLE `wallPosts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `parentID` int(11) DEFAULT NULL,
    `commenterID` varchar(255) NOT NULL,
    `userID` varchar(255) DEFAULT NULL,
    `post` text,
    `tags` varchar(255) DEFAULT NULL,
    `date` datetime NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
  --------------------------------------------------------
  
  INSERT INTO `wallPosts` VALUES(1, NULL, '1', '1', 'This is a status update', NULL, '2013-07-23 08:34:28');
  INSERT INTO `wallPosts` VALUES(3, 1, '1', '1', 'this is a reply to a status update', NULL, '2013-07-23 08:35:39');
  INSERT INTO `wallPosts` VALUES(4, 1, '2', '1', 'this is a reply to the status update from another user', NULL, '2013-07-15 11:57:35');
  INSERT INTO `wallPosts` VALUES(5, NULL, '1', '2', 'This is a post on a user\\''s wall, should not be displayed on main page', NULL, '2013-07-23 08:37:28');
  INSERT INTO `wallPosts` VALUES(6, 5, '1', '2', 'this is a reply to a wall post, should not be displayed on main', NULL, '2013-07-23 08:37:44');
  </code></pre>
  
  <p>This is what I currently have:</p>
  
  <h2><img src="https://i.stack.imgur.com/7ctcq.png" alt="This is what I have"></h2>
  
  <p>This is what I want:</p>
  
  <h2><img src="https://i.stack.imgur.com/V3wTu.png" alt="This is what I want"></h2>
  
  <p>This is the query I have been using:</p>
  
  <pre><code>SELECT
  
  `p`.`id`,
  `p`.`parentID`,
  `p`.`commenterID`,
  `p`.`userID`,
  `p`.`post`,
  
  case
  when (`p`.`userID` = `p`.`commenterID` and `p`.`parentID` IS NULL)
  then 'true'
  else 'false'
  end as isMain
  
  FROM `wallPosts` `p`
  
  LEFT JOIN `wallPosts` `c` ON `c`.`parentID` = `p`.`parentID` AND `c`.`id` &gt; `p`.`id`
  
  GROUP BY `p`.`id`, `p`.`parentID`
  
  HAVING COUNT(`c`.`id`) &lt; 10
  </code></pre>
  
  <p>This limits the number of replies to the most recent 10.</p>
  
  <p>Thanks for your help! :)</p>