Selection Query Help

[Anthop]

I have a particular query that I'm wondering will work as I hope.  Could a helpful forum-goer take a look, please?

 

Let's say I'm trying to implement a forum, and I have a Forum table like so:

┌─────────────┐
│ Forum       │
│-------------│
│ ForumID     │
│ Title       │
│ Description │
└─────────────┘

 

And a Thread table like so:

┌─────────────┐
│ Thread      │
│-------------│
│ ThreadID    │
│ ForumID     │
│ Title       │
└─────────────┘

 

And finally a Post table like so:

┌─────────────┐
│ Post        │
│-------------│
│ PostID      │
│ ThreadID    │
│ User        │
│ DatePosted  │
└─────────────┘

With the obvious foreign-key relationships.

 

Now, I need a query that displays the threads in a particular forum reverse-sorted by date of a thread's most recent post.  The best way to do this would be if the rows returned also contained the columns from the post table for that most recent post.  I've gotten this far, but I'm not sure if the following query does what I want it to do.  Is there any guarantee that the following query will correctly select the most recent post in association with a distinct thread or just any post from that thread?  Also, since this query returns columns with the same name, will referencing that column in the PHP associative array have different or special behavior?

SELECT Thread.*, Post.*, DISTINCT Thread.ThreadID FROM Thread
INNER JOIN Post ON Thread.ThreadID = Post.PostID
WHERE Thread.ForumID='somegivenforumid'
ORDER BY Post.DatePosted DESC

 

If it matters any, I'm using MySQL 4.  Any help would be greatly appreciated!  Thank you beforehand .

[Barand]

SELECT f.Description, t.Title, p.*

FROM post p

    INNER JOIN thread t ON p.ThreadID = t.ThreadID

    INNER JOIN forum f ON t.ForumID = f.ForumID

WHERE t.ForumID = '$somegivenforumid'

ORDER BY p.DatePosted DESC

[Anthop]

@ Barand:  Wouldn't that just create a ton of rows, one for each post, with duplicate entries for each thread and each forum?

[Barand]

Sorry, missed this bit

 

Now, I need a query that displays the threads in a particular forum reverse-sorted by date of a thread's most recent post.

 

I'll re-think.

[Barand]

SELECT f.Description, t.Title, MAX(p.DatePosted) as lastpost
FROM post p
    INNER JOIN thread t ON p.ThreadID = t.ThreadID
    INNER JOIN forum f ON t.ForumID = f.ForumID
WHERE t.ForumID = '$somegivenforumid'
GROUP BY f.Description, t.Title
ORDER BY lastpost DESC