Hello need help with on how to do this in SQL Server News from Forum Topics/Messages

Question

Okay I have three tables that all communicate with each other.

ForumTopic t
ForumMessage m
ForumUser u

What I am trying to do is get the first message of each Topic.

I have tried this

  SELECT
   m.[Message], m.[TopicID], m.[Posted], u.Name, t.[Views], t.NumPosts,
   t.Topic
   FROM [ForumMessage] m
   INNER JOIN ( SELECT TopicID, Topic, [Views], NumPosts, ForumID
    FROM [ForumTopic]
    GROUP BY TopicID, Topic, [Views], NumPosts, ForumID ) t ON t.TopicID = m.TopicID
   INNER JOIN [ForumUser] u
   ON u.UserID = m.UserID
   WHERE t.ForumID IN(1,2)
   ORDER BY m.Posted DESC;

And the Result is listed below

 Message    TopicID Posted Name Views NumPosts Topic
    6 2009-07-20 18:14:06.270 Ravenal 26 3 GENESIS 2.5.1a RELEASE
    6 2009-07-20 18:08:51.027 Ryan 26 3 GENESIS 2.5.1a RELEASE
    6 2009-07-20 17:06:33.550 Ravenal 26 3 GENESIS 2.5.1a RELEASE
    4 2009-07-17 14:22:47.560 Ravenal 14 1 MyGameTools IRC
    3 2009-07-17 01:09:22.403 Ravenal 43 1 GENESIS 2.5.0b RELEASE
    2 2009-07-17 00:48:30.873 Ravenal 44 2 GENESIS 2.5.0a RELEASE
    2 2009-07-16 23:08:44.830 Ravenal 44 2 GENESIS 2.5.0a RELEASE
    1 2009-07-16 23:03:11.790 Ravenal 20 1 Welcome to MyGameTools

So I am trying to figure out how to make it so that it ends up looking like this

 Message    TopicID Posted Name Views NumPosts Topic
    6 2009-07-20 18:14:06.270 Ravenal 26 3 GENESIS 2.5.1a RELEASE
    4 2009-07-17 14:22:47.560 Ravenal 14 1 MyGameTools IRC
    3 2009-07-17 01:09:22.403 Ravenal 43 1 GENESIS 2.5.0b RELEASE
    1 2009-07-16 23:03:11.790 Ravenal 20 1 Welcome to MyGameTools

Any help will be much appreciated.

Answer 1

It is giving multiple records for a message because you are JOINing it with user table & message has multiple users (other than Topic 4,3,1).

EDIT: You will have to reduce the output of outer query by limiting to give 1 record (as per max(PostedDateTime).

Answer 2

Have you tried using a derived table to get the max posted datetime for each topic from the ForumMessages table?

eg

SELECT   m.[Message], m.[TopicID], m.[Posted], u.Name, t.[Views], t.NumPosts,   t.Topic   FROM 
( SELECT TopicID, Max(Posted) MaxPosted
  FROM ForumMessage
  GROUP BY TopicID ) MaxMessage
INNER JOIN [ForumMessage] m   
  ON m.TopicID = maxMessage.TopicID
  AND m.Posted = maxMessage.MaxPosted
INNER JOIN ( SELECT TopicID, Topic, [Views], NumPosts, ForumID
    FROM [ForumTopic]
    GROUP BY TopicID, Topic, [Views], NumPosts, ForumID ) t 
  ON t.TopicID = m.TopicID
INNER JOIN [ForumUser] u
   ON u.UserID = m.UserID   
WHERE t.ForumID IN(1,2)   
ORDER BY m.Posted DESC;

Answer 3

For reference for those who may do the same exact thing I was doing... This was my solution to my result, after the help from Darren.

SELECT  m.[Message], m.[TopicID], m.[Posted], u.Name, t.[Views], t.NumPosts,   t.Topic   FROM 
( SELECT TopicID, Min(Posted) MaxPosted
  FROM ForumMessage
  GROUP BY TopicID ) MaxMessage
INNER JOIN [ForumMessage] m   
  ON m.TopicID = maxMessage.TopicID
  AND m.Posted = maxMessage.MaxPosted
INNER JOIN [ForumTopic] t
  ON t.TopicID = m.TopicID
INNER JOIN [ForumUser] u
   ON u.UserID = m.UserID   
WHERE t.ForumID IN(1,2)  
ORDER BY m.[Posted] DESC