How to merge two tables based on common column and sort the results by date

Question

Hello friends, I have two mysql tables and i want to merge the results of these two tables based on the common column rev_id. The merged results should be sorted by the date of two tables.

Please help me.

CREATE TABLE  `reply` (
  `id` int(3) NOT NULL auto_increment,
  `name` varchar(25) NOT NULL default '',
  `member_id` varchar(45) NOT NULL,
  `rev_id` int(3) NOT NULL default '0',
  `description` text,
  `post_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `flag` char(2) NOT NULL default 'N',
  PRIMARY KEY  (`id`),
  KEY `member_id` (`member_id`)
) ENGINE=MyISAM;

CREATE TABLE `comment` (
  `com_id` int(8) NOT NULL auto_increment,
  `rev_id` int(5) NOT NULL default '0',
  `member_id` varchar(50) NOT NULL,
  `comm_desc` text NOT NULL,
  `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`com_id`),
  KEY `member_id` (`member_id`)
) ENGINE=MyISAM;

Answer 1

Try this:

SELECT * FROM reply LEFT JOIN comment ON reply.rev_id = comment.rev_id ORDER BY reply.post_date, comment.date_created