Welcome to WebmasterWorld Guest from 54.242.63.214

Forum Moderators: open

Message Too Old, No Replies

Help with JOIN

     

branner

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

5+ Year Member



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.

branner

2:33 pm on Feb 12, 2010 (gmt 0)

5+ Year Member



I would really appriciate, if someone could help me with a solution.

whoisgregg

10:38 pm on Feb 18, 2010 (gmt 0)

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



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
 

Featured Threads

Hot Threads This Week

Hot Threads This Month