joined:Feb 11, 2010
I really need help with this one. I think it's quite simple for the experienced user... This is a piece of SQL from a typical community board, used to select the latest ten threads with some extra info about users etc.:
SELECT tf.*, tt.*, tu.user_id,user_name, MAX(tp.post_id) as last_id, COUNT(tp.post_id) as count_posts FROM forums tf
INNER JOIN threads tt USING(forum_id)
INNER JOIN posts tp USING(thread_id)
INNER JOIN users tu ON tt.thread_lastuser=tu.user_id
WHERE tt.forum_id != 26 AND ".groupaccess('forum_access')." GROUP BY thread_id ORDER BY thread_lastpost DESC LIMIT 0,10"
As you see, the tables involved are "forums", "threads", "posts" and "users".
OK, what I want now is to add, without using an extra SELECT-statement, the content of the latest post from the "posts"-table. That content is called "post_message" in the table. As you can guess, that last post has the same post_id as the one found with "MAX(tp.post_id) as last_id".
Could I add another INNER JOIN? And how?
I hope you understand the issue. I want to limit the load on the server. It could be easy to solve with an extra select for each row, but that would give 11 calls to the database, just to load this simple box...
Just adding tp.post_message to the original select won't help. I want the LAST post, not the FIRST.