title: MySQL JOIN to pull up latest posts by comparing post dates and comments dates
body: <p>Well, I have two tables community and community_comments.
  community is the table where the topic title and other details of the topic is stored.
  community_details is the table in which all the posts or comments for the topic/thread is stored.</p>
  
  <p>I need to pull up the latest five topics based on the comments date for threads as well as date of the original topic/thread. </p>
  
  <p>Now there maybe some threads which do not have any comments yet, but are more recent than some threads which do have comments. I need to pull them up correctly.</p>
  
  <p>I tried queries such as </p>
  
  <pre><code>SELECT MAX(community_comments.id), `community`.*
  FROM (`community`)
  LEFT JOIN `community_comments` ON `community`.`id`=`community_comments`.`community_id`
  WHERE `community`.`type` = 1
  GROUP BY `community_comments`.`id`
  ORDER BY `community_comments`.`date_posted` DESC
  LIMIT 5 
  </code></pre>
  
  <p>this pulls up the same thread multiple times, and this </p>
  
  <pre><code>SELECT MAX(community_comments.id), `community`.*
  FROM (`community`)
  LEFT JOIN `community_comments` ON `community`.`id`=`community_comments`.`community_id`
  WHERE `community`.`type` = 1
  GROUP BY `community_comments`.`community_id`
  ORDER BY `community_comments`.`date_posted` DESC
  LIMIT 5 
  </code></pre>
  
  <p>pulls up unique threads but doesnt pull up the correct latest ones.</p>
  
  <p>The table structure for community is :</p>
  
  <pre><code>CREATE TABLE `community` (   
    `id` varchar(12) character set utf8 NOT NULL,   
    `title` varchar(255) character set utf8 NOT NULL,   
    `content` text character set utf8 NOT NULL,   
    `author` varchar(13) character set utf8 NOT NULL,   
    `category` int(10) unsigned NOT NULL,   
    `type` tinyint(1) unsigned NOT NULL default '1' COMMENT '1 = Forum; 2 = Site Help; 3 = Local &amp; Global',   
    `location` varchar(100) character set utf8 NOT NULL,   
    `country` int(10) unsigned NOT NULL,   
    `date_posted` datetime NOT NULL,   
    PRIMARY KEY  (`id`)   
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;   
  </code></pre>
  
  <p>and table structure for community_comments is : </p>
  
  <pre><code>CREATE TABLE `community_comments` (   
    `id` varchar(12) character set utf8 NOT NULL,   
    `community_id` varchar(12) character set utf8 NOT NULL,   
    `content` text character set utf8 NOT NULL,   
    `member_id` varchar(13) character set utf8 NOT NULL,   
    `type` tinyint(1) unsigned NOT NULL default '1' COMMENT '1 = Forum; 2 = Site Help; 3 =  Local &amp; Global',   
    `quoted` varchar(12) character set utf8 NOT NULL COMMENT 'Id number of the comment that is being quoted',   
    `date_posted` datetime NOT NULL,   
    PRIMARY KEY  (`id`)   
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;   
  </code></pre>
  
  <p>Any help would be greatly appreciated. Thanks.</p>