CODEWITHSUNDEEP
mysqlgroup-by
Amitabh
Amitabh

Reputation: 739

MySQL JOIN to pull up latest posts by comparing post dates and comments dates

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.

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.

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.

I tried queries such as 

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

this pulls up the same thread multiple times, and this 

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

pulls up unique threads but doesnt pull up the correct latest ones.

The table structure for community is :

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

and table structure for community_comments is : 

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

Any help would be greatly appreciated. Thanks.

Upvotes: 1

Views: 118

Answers (2)

Amitabh
Amitabh

Reputation: 739

SELECT c.*, IFNULL(MAX(com.date_posted),c.date_posted) as last_post 
            FROM community c 
            LEFT OUTER JOIN community_comments com 
            ON com.community_id = c.id 
            GROUP BY c.id 
            ORDER BY last_post DESC 
            LIMIT 5 ;

Upvotes: 0

Kevin McKelvin
Kevin McKelvin

Reputation: 3547

So if I'm understanding correctly, you need the community fields that have the 5 most recent community_comments on them. You want to use the SQL Group By to get this right.

SELECT c.*, MAX(com.date_posted) as last_post
FROM community c
LEFT OUTER JOIN community_comments com
ON com.community_id = c.id
GROUP BY c.id
ORDER BY MAX(com.date_posted) DESC
LIMIT 5

If you don't want to show communities that have no comments, you can replace the left outer join with an inner join.

Upvotes: 2

Related Questions