View Properties

Name	Value
Owner	dbo
Created	2004-05-31

Columns

Name	Data Type	Length
Approved	bit	1
Body	ntext	16
ForumID	int	4
ForumName	nvarchar	100
IsLocked	bit	1
IsPinned	bit	1
MostRecentPostAuthor	nvarchar	50
MostRecentPostID	int	4
ParentID	int	4
PinnedDate	datetime	8
PostDate	datetime	8
PostID	int	4
PostLevel	int	4
PostType	int	4
Replies	int	4
SortOrder	int	4
Subject	nvarchar	256
ThreadDate	datetime	8
ThreadID	int	4
TotalViews	int	4
UserName	nvarchar	50

Total: 21 column(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE VIEW forums_Post
AS
    SELECT
        Subject,
        Body, 
        PostID, 
        ThreadID, 
        ParentID,
        TotalViews, 
        IsLocked, 
        IsPinned,
        ThreadDate, 
        PinnedDate, 
        UserName,
        ForumID,
        PostLevel,
        SortOrder,
        Approved,
        PostType,
        PostDate,
        (
            SELECT Name
            FROM Forums
            WHERE ForumID = P.ForumID
        ) AS ForumName,
        (
            SELECT COUNT(*)
            FROM Posts
            WHERE P.ThreadID = ThreadID AND Approved = 1 AND PostLevel != 1
        ) AS Replies, 
        (
            SELECT TOP 1 Username
            FROM Posts
            WHERE P.ThreadID = ThreadID AND Approved = 1
            ORDER BY PostDate DESC
        ) AS MostRecentPostAuthor,
        (
            SELECT TOP 1 PostID
            FROM Posts
            WHERE P.ThreadID = ThreadID AND Approved = 1
            ORDER BY PostDate DESC
        ) AS MostRecentPostID
    FROM Posts P

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO