Using Max in multiple join

[capone67]

Hi All

I have an SQL query that has several joins in it. It is for a forum. I want to select the record for the latest post in a thread along with the titles and ids of the thread/forum/forumarea. My query however is returning the content of the first post and the ID of the latest post. How to I get all of the data returned for the latest post?

SELECT max(Posts.ID) as PostID, ForumAreas.Title as ForumAreaTitle, ForumAreas.ID as ForumAreaID, Forums.Title As ForumTitle, Forums.ID As ForumID, Threads.Title as ThreadTitle, Threads.ID, MonitorThreads.UpdateDate, MonitorThreads.Email, MonitorThreads.Viewed, Posts.UserIndex, Posts.Title, Posts.Content FROM (Users inner JOIN MonitorThreads ON Users.ID = MonitorThreads.UserID) inner JOIN (Posts inner JOIN (Threads inner JOIN (Forums inner JOIN ForumAreas ON Forums.ForumAreaID = ForumAreas.Id) ON Threads.ForumID = Forums.ID) ON Posts.ThreadID = Threads.id) ON MonitorThreads.ThreadID = Threads.id where MonitorThreads.userid=13

 

[r937]

SELECT Posts.ID AS PostID
     , ForumAreas.Title AS ForumAreaTitle
     , ForumAreas.ID AS ForumAreaID
     , Forums.Title AS ForumTitle
     , Forums.ID AS ForumID
     , Threads.Title AS ThreadTitle
     , Threads.ID
     , MonitorThreads.UpdateDate
     , MonitorThreads.Email
     , MonitorThreads.Viewed
     , Posts.UserIndex
     , Posts.Title
     , Posts.Content 
  FROM Users 
INNER 
  JOIN MonitorThreads 
    ON MonitorThreads.UserID = Users.ID
INNER 
  JOIN Threads
    ON Threads.id = MonitorThreads.ThreadID
INNER 
  JOIN Forums 
    ON Forums.ID = Threads.ForumID
INNER 
  JOIN ForumAreas 
    ON ForumAreas.Id = Forums.ForumAreaID
INNER
  JOIN ( SELECT ThreadID
              , MAX(ID) AS max_PostID
           FROM Posts
         GROUP
             BY ThreadID ) AS p
    ON p.ThreadID = Threads.id
INNER
  JOIN Posts 
    ON Posts.ThreadID = Threads.id
   AND Posts.ID = p.max_PostID
 WHERE Users.ID = 13

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon