CREATE PROCEDURE dbo.[Forum_Thread_Get] ( @ThreadID INT )
AS
    SELECT  [Subject],
            Body,
            CreatedDate,
            UserID AS StartedByUserID,
            T.ThreadID,
            ForumID,
            [Views],
            LastPostedPostID AS LastApprovedPostID,
            ( SELECT    COUNT(ThreadID)
              FROM      dbo.Forum_Posts
              WHERE     ThreadID = T.ThreadID
                        AND PostID <> T.ThreadID
                        AND IsApproved = 1
            ) AS Replies,
            IsPinned,
            PinnedDate,
            IsClosed,
            ThreadStatus,
            AnswerPostID,
            AnswerUserID,
            PollID,
            AnswerDate,
            SitemapInclude,
            ISNULL(( SELECT MAX(ThreadID)
                     FROM   dbo.Forum_Threads
                     WHERE  ThreadID < T.ThreadID
                            AND ForumID = T.ForumID
                   ), 0) AS PreviousThreadID,
            ISNULL(( SELECT MIN(ThreadID)
                     FROM   dbo.Forum_Threads
                     WHERE  ThreadID > T.ThreadID
                            AND ForumID = T.ForumID
                   ), 0) AS NextThreadID,
            ISNULL(( SELECT COUNT(ThreadID)
                     FROM   dbo.Forum_ThreadRating TR
                     WHERE  TR.ThreadID = T.ThreadID
                   ), 0) AS RatingCount,
            ISNULL(( SELECT AVG(Rate)
                     FROM   dbo.Forum_ThreadRating TR
                     WHERE  TR.ThreadID = T.ThreadID
                   ), 0) AS Rating,
            1 AS TotalRecords,
            CI.CreatedByUserID,
            CI.CreatedOnDate,
            CI.ContentKey,
            CI.Indexed,
            CI.Content,
            CI.ContentItemID,
            CI.LastModifiedByUserID,
            CI.LastModifiedOnDate,
            CI.ModuleID,
            CI.TabID,
            CI.ContentTypeID
    FROM    dbo.Forum_Threads T
            INNER JOIN dbo.Forum_Posts P ON T.ThreadID = P.PostID
            LEFT JOIN dbo.ContentItems CI ON T.ContentItemID = CI.ContentItemID
    WHERE   P.PostID = @ThreadID

GO