Dynamic join syntax

[virtual_odin]

I'm probably missing something obvious but I cannot find a simple solution to this issue.  I have two tables, users and comments.  In the latter is a column for user id, so I have a query with a LEFT JOIN and the COUNT function to find out how many comments each user has made.  I am also using the MAX function to know when the most recent comment was.  So far so good.  What I would like is to pull out a field from the comments table that corresponds to the latest comment made by each user.

 

Here's the syntax so far

 

SELECT `users`.*, `countries`.*, 
	COUNT(comments.author) AS ct, 
	MAX(comments.submitted) AS last_comment
FROM `users`
LEFT JOIN `countries` ON  `users`.`country_code` = `countries`.`country_code` 
LEFT JOIN `comments` ON `users`.`id` = `comments`.`author`
GROUP BY `users`.`id`
ORDER BY `nickname` ASC

 

As ever any help would be much appreciated.

[fenway]

You're almost there... but you can't get back * -- it's meaningless.  Once you have the value for the last comment, you need to join back to the comments table.

[virtual_odin]

you can't get back * -- it's meaningless.

In all my queries * returns all the values in the row.

Once you have the value for the last comment, you need to join back to the comments table

is what I am looking for help with, please.

[fenway]

Yes, and * will return garbage when used with GROUP BY -- as you've discovered.

 

Like I said, take the details of your "latest" or "max" comment, and find that comment again in the comments table, without a group by.