Complex Query

[asfaw]

Hello all,

I have the following MyISAM tables:

Forum
(forumid, name, description)
Post
(postid, forumid, userid, topicid, subject, message, date)
Topic
(topicid, userid, forumid, subject)
User
(userid, login, password)

I am trying to get a list of all the forums groupd by forum name, total count of post for each forum, total topics for each forum, the login (i.e. the last person who posted in each forum), and the last post date.


I have done a good part of it. I need your help on how to pull the last person who posted in each forum and the last post date.

SELECT
Forum.forumId as forumId, Forum.name as name ,description,
count(DISTINCT Topic.topicid) as countTopics,
count(DISTINCT Post.postid) as countPosts,
DATE_FORMAT(Max(Post.date), '%M %d, %Y %h:%i %p') as date,
login

FROM Forum, Topic, Post, User

WHERE Forum.forumId = Topic.forumId
AND Forum.forumId = Post.forumId
GROUP BY name;

Thank you very much.