[SOLVED] Retrieving All Threads And First Posts

[Dom]

Hi all,

I've been given the "simple, five minute" task of building some basic forum/bulletin board software. But I'm having trouble building the page that shows users all the threads.

What I need is a page that shows all the current threads (well, the top 10 most recent actually, but that's not the hard part right now). I need the page to display the thread title, and the first post in the thread.

What I have is two SQL Server 2005 tables: one table stores the thread info, and the other table stores into about each post. They look like so:



The Posts table has a foreign key (InReplyToThreadID) which links to the 'ThreadID' in the Threads table. That's how the posts are related to the threads.

There can be lots of posts for any given thread, but what I need is a single SQL query (using T-SQL) that gives me all thread titles, and then the first post of each of the threads.

I've not really used anything much more complicated than JOINS in SQL queries, so I'm a bit stuck. I can join the tables, but any query I write seems to return either the thread titles with the very latest post (that doesn't match the threads) or it returns every post and associated thread title, so I get rows with repeated titles.

Anyone know how to get just the thread title and first post of that thread, for all threads?

[Dom]

Got the shizzle working, with a lot of help from peeps on Twitter:

Code:

SELECT tblBulletinBoardThread.ThreadID,
tblBulletinBoardThread.ThreadTitle,
tblBulletinBoardPost.PostText
FROM tblBulletinBoardPost
INNER JOIN tblBulletinBoardThread 
ON tblBulletinBoardThread.ThreadID = tblBulletinBoardPost.InReplyToThreadID
WHERE PostID IN
(SELECT MIN(PostID) AS 'PostID'
FROM tblBulletinBoardThread
INNER JOIN tblBulletinBoardPost
ON tblBulletinBoardThread.ThreadID = tblBulletinBoardPost.InReplyToThreadID
GROUP BY ThreadID)
ORDER BY tblBulletinBoardPost.DateTimePosted DESC;