Get data from two tables depending on data from one in a single query

Question

I'm making a simple forum, and in the listing of the threads i want the classical "last post by (user) at (date)" to show. I'm using only one table to contain all the posts in the forum, where posts with the parentPost-field not equal to null are replies to a thread.

These are the tables and necessary fields i need data from:

forumPosts

• id: integer, primary key
• title: string value
• date: string value
• parentPost: integer (This field = ID of the initial post in the thread. If it's NULL, the post is an "initial thread post")
• author: integer, foreign key to the users table
• countAnswers: integer
• lastPost: integer, (This field = the ID of the last post which was replied to it. Only used when the parentPost-field = NULL)

users

• userid: integer, primary key
• firstName: string value
• lastName: string value

As of now, i have this query:

SELECT forumPosts.id, forumPosts.title, forumPosts.date, forumPosts.author, forumPosts.countAnswers, users.firstName, users.lastName 
FROM forumPosts 
INNER JOIN users ON forumPosts.author = users.userid 
WHERE forumPosts.parentPost IS NULL ORDER BY id DESC;

Returns all I need (ID, Title, Date, Author and how many replies it has), except for the date and author of the last posted reply.

In addition, I'd like to fetch the following:

The date from the last post in the thread, that is the post with the highest ID and parentPost = forumPosts.id. I'd also want to fetch the firstName and lastName from the user that posted this reply.

I've been fooling around now for a long time with different joins and derived tables and such, but I can't seem to get this right. Ideally, i'd want a table returned with the following fields:

• id
• title
• date
• firstName
• lastName
• countAnswers
• lastReplyDate
• lastReplyFirstName
• lastReplyLastName

To me, it seems possible, but I obviously can't make it.

I hope you understood my somewhat complex question, and i thank you a lot for all answers.

The sollution

SELECT        forumPosts.id, forumPosts.title, forumPosts.date, forumPosts.author, forumPosts.countAnswers, users.firstName, users.lastName, 
                     lastPost.date AS lastPostDate, lastUser.firstName AS lastPostFirstName, lastUser.lastName AS lastPostLastName
FROM            forumPosts AS lastPost INNER JOIN
                     users AS lastUser ON lastPost.author = lastUser.userid INNER JOIN
                     forumPosts INNER JOIN
                     users ON forumPosts.author = users.userid ON lastPost.id = forumPosts.lastPost
WHERE        (forumPosts.parentPost IS NULL)
ORDER BY forumPosts.id DESC

That worked. So typical, finding my own answer a minute after i've spent a lot of time writing a post here :-\

Answer 1

If "thread" is an important concept to your system--and it appears that's the case--I would model it as a separate entity. Identifying threads by posts that have no parent is going to cause you grief as you're just beginning to discover.

For one thing to find all the threads:

SELECT ... FROM posts WHERE parent IS NULL

depending on database vendor isn't typically performant because NULL values usually aren't indexed.

Create a Thread table and all these problems go away.

Answer 2

SELECT f.id, f.title, f.date, 
 u1.firstName, u1.lastName, f.countAnswers,
 r1.date AS lastReplyDate,
 u2.firstName AS lastReplyFirstName
 u2.lastName AS lastReplyLastName
FROM forumPosts f
 INNER JOIN users u1 ON (f.author = u1.userid)
 LEFT OUTER JOIN (forumPosts r1 INNER JOIN users u2 ON r1.author = u2.userid)
  ON (f.id = r1.parentPost)
 LEFT OUTER JOIN forumPosts r2 
  ON (f.id = r1.parentPost AND r1.id < r2.id)
WHERE f.parentPost IS NULL 
 AND r2.id IS NULL
ORDER BY f.id DESC;