Welcome to WebmasterWorld Guest from 54.160.177.33

Forum Moderators: open

Message Too Old, No Replies

Help with JOIN

     
1:32 am on Feb 11, 2010 (gmt 0)

New User

5+ Year Member

joined:Feb 11, 2010
posts:2
votes: 0


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.
2:33 pm on Feb 12, 2010 (gmt 0)

New User

5+ Year Member

joined:Feb 11, 2010
posts:2
votes: 0


I would really appriciate, if someone could help me with a solution.
10:38 pm on Feb 18, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Dec 9, 2003
posts:3416
votes: 0


Welcome to WebmasterWorld, branner!

Just adding tp.post_message to the original select won't help. I want the LAST post, not the FIRST.

If I understand you correctly, you can add tp.post_message to the original select as long as you reverse the sort order:

ORDER BY thread_lastpost ASC