MySQL SELECT with a condition help

CREATE TABLE `comments` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `comment_parent_id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `comment_text` varchar(200) NOT NULL DEFAULT '',
  `comment_created` int(20) NOT NULL DEFAULT '0',
  `comment_updated` int(20) NOT NULL DEFAULT '0',
  `comment_replies_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

Each comment can have multiple replies, however, replies cannot be replied to. So when someone replies to a comment, the row they insert will have the ID for the comment they replied to in the parent ID column.

I would like to retrieve all comments and if the comment has replies, I would like to retrieve the last reply.

SELECT c1.* 
FROM comments c1 
WHERE comment_parent_id = '0' 
ORDER BY comment_created DESC;

So if c1.comment_replies_count > 0 I would like to... 

SELECT c2.* 
FROM comments c2 
WHERE comment_parent_id = c1.comment_id 
ORDER BY comment_created DESC Limit 1;

Can this be achieved in 1 query? Or Is it best to do another call to the database, during the php loop statement, to fetch the last reply to the comment?

Thanks in advance and please pardon my ignorance as I'm learning still.

From stackoverflow

• Try a sub-select:

  SELECT * FROM comments WHERE comment_parent_id in (
  SELECT c1.comment_id 
  FROM comments c1 
  WHERE c1.comment_parent_id = '0' 
  AND c1.comment_replies_count > 0
  ORDER BY comment_created DESC)
  ORDER BY comment_created DESC Limit 1;
  

• You can join the table back onto itself:

  SELECT c1.*, c2.*, MAX(c2.comment_id)
  FROM comments c1 LEFT JOIN comments c2 ON c1.comment_id = c2.comment_parent_id
  WHERE c1.comment_parent_id = '0' 
  GROUP BY c1.comment_id
  ORDER BY c1.comment_created DESC
  

  Allain Lalonde : Dang, beat me to it.

  Seth : This kinda works, but it is not retrieving the last reply -always the first. eg: comment (ID 1, parent ID 0, text "Hello") first reply (ID 2, parent ID 1, text "Reply to comment 1") second reply (ID 3, parent ID 1, text "Another reply to comment 1") Even though MAX is returning 3, it returns the ID and text from the first reply.

  acrosman : Try adding a clause to the WHERE clause to compare the MAX() to the c2.comment_id.