CREATE PROCEDURE dbo.[Forum_Thread_GetByForum]
    (
      @ForumID INT,
      @UserID 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   ForumID = @ForumID

GO