Help with JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with JOIN

Author

Topic

branner
Starting Member

6 Posts

Posted - 2010-02-10 : 18:56:05

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...

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-02-10 : 19:07:58

If I understand the code properly, don't you just need to add tp.post_message to your original select?

The posts table has already been joined, right?

branner
Starting Member

6 Posts

Posted - 2010-02-10 : 19:24:02

Thanks for your suggestion.

But if I do that, the post_message from the FIRST post will be selected. I want to select the LAST post, that is, the one with the same post_id as last_id. Maybe it's easier to understand, when you know, what i'm going to use it for. I need it, because I want to show the beginning of the latest post, when the curser hovers over the title in the thread overview...
A very common forum feature, but not in my CMS.

Can be difficult to explain, but hopefully you understand now. :)

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-02-10 : 22:06:01

It seems to me we should be able to do this via a sub query or another join of posts with a different alias...

I'm just not sure how. This is a good problem, though. I will keep working on it.

mymatrix
Starting Member

24 Posts

Posted - 2010-02-11 : 01:06:08

Just want to know is the provided query executing without any error?
since you have used groupby on thread id and fetching all the columns from tf and tt.

But to above mentioned problem, please try below mentioned query:

SELECT tf.*, tt.*, tu.user_id,user_name,
tp.last_id, tp.count_posts,tp.post_message FROM forums tf
INNER JOIN threads tt USING(forum_id)
INNER JOIN
(select MAX(tp2.post_id) as last_id,
COUNT(tp.post_id) as count_posts,
post_message=(select tp1.post_message from posts tp1 where tp1.Post_Id=tp2.PostId)
from posts tp2 group by tp2.post_id) 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

**************************************

Even my blood group says be -ve to all the negatives.

branner
Starting Member

6 Posts

Posted - 2010-02-11 : 07:22:52

@mymatrix
Yes, the original query works fine.
I tried your suggestion, and it gives the error: "Unknown table 'tp1' in field list".
If it should work, wouldn't it give a heavy load on the server with two nested selects inside each other? As said, I could easily fix it with php and a new query for each row. But I want to do it with a join, if possible.
But thanks, anywhay :)

I tried:

SELECT tf.*, tt.*, tu.user_id,user_name, tp2.post_message AS last_message, 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
INNER JOIN posts tp2 ON MAX(tp.post_id)=tp2.post_id
WHERE tt.forum_id != 26 AND ".groupaccess('forum_access')." GROUP BY thread_id ORDER BY thread_lastpost DESC LIMIT 0,10"

But i get a "group by" error. :(

mymatrix
Starting Member

24 Posts

Posted - 2010-02-11 : 07:36:42

branner...It can be done definately with joins.

will it be possible for you to provide the table structure.

And secondly, you would get group by error because you are grouping the resultset on the basis of thread_id and in the select statment you are mentioning all the records of tf and tt table.

I guess, proving table structure would really help in providing solution to given problem.

**************************************

Even my blood group says be -ve to all the negatives.

branner
Starting Member

6 Posts

Posted - 2010-02-11 : 08:54:21

Four tables are involved:

1) "forums", with the columns:
forum_id forum_cat forum_name forum_order forum_description forum_moderators forum_access forum_posting forum_lastpost forum_lastuser

2) "threads", with the columns:
forum_id thread_id thread_subject thread_author thread_views thread_lastpost thread_lastuser thread_sticky thread_locked

3) "posts", with the columns:
forum_id thread_id post_id post_subject post_message post_showsig post_smileys post_author post_datestamp post_ip post_edituser post_edittime

4) "users", with a lot of personalized columns (irrelevant here).

I hope you can use it...

branner
Starting Member

6 Posts

Posted - 2010-02-11 : 17:04:18

Anyone else with a solution?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 17:12:04

which version of SQL server are you using?

branner
Starting Member

6 Posts

Posted - 2010-02-11 : 18:02:29

MySQL version 4.1

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-02-13 : 12:13:08

Try it with a derived table...

SELECT tf.*, tt.*, tu.user_id,user_name, tp2.post_message AS last_message, 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
INNER JOIN posts tp2 ON tp2.post_id = (SELECT MAX(posts.post_id) from posts)
WHERE tt.forum_id != 26 AND ".groupaccess('forum_access')." GROUP BY thread_id ORDER BY thread_lastpost DESC LIMIT 0,10"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources