Designing a forum: Ordering my topics

[Scorez2000]

Hi,

I've started developing my new discussion board, nut I am having trouble ordering the list of topics.

It's all very simple so far. I have 3 tables (users, threads and replies).

The thread table contains the title of the thread, the contents of the first post and the date and time of the post.

Then the replies table has all the replies to that thread.

Here is a screenshot of my query so far:

http://www.club-nut.co.uk/query.asp

The thing is, if I order by the date and time in the thread table, then threads will not 'bump' to the top when someone posts in them.

I cannot sort by the field in the replies folder either as it will have some blank fields in the records that are actually the first post.

Does anyone know how to create a query that will output the username and name of the thread, and order them by the date of the last reply in each thread?

Thanks in advance,

Wayne.

 

[r937]

http://www.club-nut.co.uk/query.asp

give "page not found"

use

... FROM threads LEFT OUTER JOIN replies

then

... ORDER BY iif( isnull(replies.replydate)
, threads.postdate
, replies.replydate )


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Scorez2000]

Sorry, link should have been

http://www.club-nut.co.uk/query.jpg

I'll try your order method though.

 

[Scorez2000]

Ok, that's working kind of well, but I need it to sort on two fields each time.

So if it's null it sorts by thread.postDate and thread.PostTime

if it's not null, it sorts by replies.postdate and replies.posttime

 

[Scorez2000]

Infact, that isn't working at all. It doesn't seem to be in any particular order.

 

[r937]

you need to combine your date and time fields into one datetime field

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Scorez2000]

I've put the database online incase anyone wants to fiddle around with it.

http://www.club-nut.co.uk/discv1.mdb

 

[Scorez2000]

This seems to do the trick:

SELECT User.Username, Thread.ThreadTitle, Max(IIf(IsNull([ReplyDateTime]),[Thread].[PostDateTime],[Replies].[ReplyDateTime])) AS Expr1
FROM [User] INNER JOIN (Replies RIGHT JOIN Thread ON Replies.ThreadID = Thread.ThreadID) ON User.UserID = Thread.AuthorID
GROUP BY User.Username, Thread.ThreadTitle
ORDER BY Max(IIf(IsNull([ReplyDateTime]),[Thread].[PostDateTime],[Replies].[ReplyDateTime])) DESC;