I'm coding a forum system and I'm trying to get the last post in a topic. The problem is I'm grouping the results on the topic id and I can't figure out a way to get the last reply to be displayed in the grouped data.
Here is my query so far:
SELECT SQL_CACHE users.user_id,
users.username,
topics.title,
topics.topic_id,
topics.previews,
topics.date_added,
posts.post_id,
last.username AS last_username,
last.user_id AS last_user_id,
MAX( posts.post_id ) AS last_post_id,
posts.date_added AS last_data
FROM `topics`
LEFT JOIN `users` ON users.user_id = topics.user_id
LEFT JOIN `posts` ON ( posts.topic_id = topics.topic_id )
LEFT JOIN `users` AS last ON ( last.user_id = posts.user_id )
WHERE fcat_id = '2'
GROUP BY topics.topic_id
解决方案
You can do something like
SELECT *, `last`.`user_id` AS last_user_id FROM
(
SELECT users.user_id,
users.username,
topics.title,
topics.topic_id,
topics.previews,
topics.date_added,
posts.post_id,
MAX( posts.post_id ) AS last_post_id,
posts.date_added AS last_data
FROM `topics`
LEFT JOIN `users` ON users.user_id = topics.user_id
LEFT JOIN `posts` ON ( posts.topic_id = topics.topic_id )
WHERE fcat_id = '2'
GROUP BY topics.topic_id
) AS `tst`
LEFT JOIN `posts` ON ( posts.post_id = tst.last_post_id )
LEFT JOIN `users` AS `last` ON ( `last`.user_id = posts.post_id )
Just set your selects properly and maybe alias the posts JOIN which is outside the subquery