homepage Welcome to WebmasterWorld Guest from 54.234.147.84
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Help with JOIN
branner




msg:4078088
 1:32 am on Feb 11, 2010 (gmt 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.

 

branner




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

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

whoisgregg




msg:4083181
 10:38 pm on Feb 18, 2010 (gmt 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved