Help with table query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Help with table query

Author

Topic

mparter
Yak Posting Veteran

86 Posts

Posted - 2002-11-05 : 09:49:44

I'm developing a forum for our organizations portal.

I have two tables Threads and Posts. What I need to do is show a list of all the threads and the details of the last post. I then want the resulting recordset to be sorted on the last post date.

Threads
--------
ThreadID
ForumID
Thread_Title

Posts
-------
PostID
ThreadID
Post_Title
Post_Started
Post_StartedBy

If I use the following:

SELECT dbo.Threads.ThreadID, dbo.Posts.Post_Posted, dbo.Posts.Post_PostedBy
FROM dbo.Posts INNER JOIN
dbo.Threads ON dbo.Posts.ThreadID = dbo.Threads.ThreadID
WHERE (dbo.Threads.ForumID = 1)

Then I get the same returned however many time there are posts to that forum. All I want is a list of each individual thread and the date, time and name of the last post to that thread. The catch is it has to be sorted on the date of the last post so that threads with new posts appear at the top of the list.

Help please?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-05 : 10:18:49

You'll have to make sure you get the last post, by somehow using the max function on your date column. A derived table might be helpful here.

EDIT: or wait for someone to do it for you...

at least I tried this time...

Edited by - andraax on 11/05/2002 10:22:45

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-05 : 10:21:15

SELECT Threads.ThreadID, lastpost = max(Posts.Post_Posted)
FROM Posts INNER JOIN
Threads ON Posts.ThreadID = Threads.ThreadID
WHERE Threads.ForumID = 1
group by Threads.ThreadID
order by lastpost desc

would do it if you only want the post date
for other details you will need a subquery
SELECT Threads.ThreadID, lastpost = Posts.Post_Posted, ...
FROM Posts INNER JOIN
Threads ON Posts.ThreadID = Threads.ThreadID
WHERE Threads.ForumID = 1
and Posts.Post_Posted = (select max(Posts2.Post_Posted from Posts Posts2 where Posts.ThreadID = Posts2.ThreadID)
order by lastpost desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-05 : 10:30:53

quote:
EDIT: or wait for someone to do it for you... at least I tried this time...

What goes around comes around...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21313

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-05 : 10:45:12

Yeah I guess so... :)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-05 : 11:06:14

Jay White
{0}

mparter
Yak Posting Veteran

86 Posts

Posted - 2002-11-05 : 11:22:37

Thanks very much for your help guys, for some reason after I posted this I just figured I would use a query containing another query! And it worked!!

Cheers :)

mparter
Yak Posting Veteran

86 Posts

Posted - 2002-11-06 : 10:59:49

I lied! (not on purpose!!) My querying of a sbuquery didn't work, aaaggghhh!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-06 : 11:33:33

This should do it:

SELECT T.ThreadID, P.Post_Posted, P.Post_PostedBy
FROM dbo.Threads T INNER JOIN dbo.Posts P ON P.ThreadID = T.ThreadID
INNER JOIN
(SELECT ThreadID, Max(Post_Posted) AS MaxDate FROM dbo.Posts GROUP BY ThreadID) M
ON P.ThreadID = M.ThreadID AND P.Post_Posted=M.MaxDate
WHERE T.ForumID = 1

Let me know if that doesn't work, I haven't been able to test it.

mparter
Yak Posting Veteran

86 Posts

Posted - 2002-11-06 : 17:42:57

OK, here' what I have so far:

SELECT TOP 100 PERCENT dbo.Staff_Threads.ThreadID, dbo.Staff_Threads.ForumID, dbo.Staff_Threads.Thread_Started, MAX(dbo.Staff_Posts.Post_Posted)
AS Post_Posted, dbo.Staff_Threads.Thread_Title
FROM dbo.Staff_Posts RIGHT OUTER JOIN
dbo.Staff_Threads ON dbo.Staff_Posts.ThreadID = dbo.Staff_Threads.ThreadID
GROUP BY dbo.Staff_Threads.ThreadID, dbo.Staff_Threads.ForumID, dbo.Staff_Threads.Thread_Started, dbo.Staff_Threads.Thread_Title
ORDER BY MAX(dbo.Staff_Posts.Post_Posted) DESC, dbo.Staff_Threads.Thread_Started DESC

The problem I have now is this.

Say a user adds a new thread to the forum, this won't appear at the top as it should. It will instead appears at the top of the Thread sort, with the threads that have posts sorted above this (confused yet?).

The easiest way to explain this is to show an example below!!

With the query above I get:

THREADID FORUMID THREAD_STARTED POST_POSTED
2 1 05 Nov 2002 11:11:28 AM 05 Nov 2002 04:44:05 PM
3 1 05 Nov 2002 11:14:21 AM 05 Nov 2002 02:15:07 PM
5 1 06 Nov 2002 10:04:05 PM <NULL>
4 1 05 Nov 2002 02:14:36 PM <NULL>

Which should be:

THREADID FORUMID THREAD_STARTED POST_POSTED
5 1 06 Nov 2002 10:04:05 PM <NULL>
2 1 05 Nov 2002 11:11:28 AM 05 Nov 2002 04:44:05 PM
3 1 05 Nov 2002 11:14:21 AM 05 Nov 2002 02:15:07 PM
4 1 05 Nov 2002 02:14:36 PM <NULL>

Is this possible or am I being overcomplicated?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources