Post reply

discusssion forum,thread,messages,totalnumber of replies last post date

  • March 17, 2012 at 5:38 pm

    #252649

    I need help with the following query

    Table 1:

    ThreadID, SectionID,OpenDate,CloseDate

    Table 2:

    MessageID, MessageTitle,MessageSender,MessageDate,MessageThread,MessageParent

    The message has messageparent=0 for the main thread

    the message which is a reply will have the messageparent is the message that the sender replied to

    Messagethread is the threadid that the message belongs to

    I want to display

    threadid, messagetitle, numberofreplies,lastpostdate for the main threads

    The following is to create the tables and insert information into them

    CREATE TABLE [ForumThread]([ThreadID] [int] IDENTITY(1,1) NOT NULL,[ThreadSection] [int] NOT NULL,[OpenDate] [datetime] NOT NULL,[CloseDate] [datetime] NOT NULL,[Deleted] [bit] NOT NULL CONSTRAINT [PK_ForumThread] PRIMARY KEY CLUSTERED

    ([ThreadID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

    ) ON [PRIMARY]

    -----------------------------------------------------

    CREATE TABLE[ThreadMessage](

    [MessageID] [int] IDENTITY(1,1) NOT NULL,

    [MessageSender] [int] NOT NULL,

    [MessageThread] [int] NOT NULL,

    [MessageParent] [int] NOT NULL,

    [MessageTitle] [nvarchar](100) NOT NULL,

    [MessageDate] [datetime] NOT NULL,

    [MessageContent] [nvarchar](max) NOT NULL,

    [UpdateDate] [datetime] NOT NULL,

    [Deleted] [bit] NOT NULL,

    CONSTRAINT [PK_ThreadMessage] PRIMARY KEY CLUSTERED

    (

    [MessageID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [ThreadMessage] WITH CHECK ADD CONSTRAINT [FK_ThreadMessage_ForumThread1] FOREIGN KEY([MessageThread])

    REFERENCES [ForumThread] ([ThreadID])

    GO

    ALTER TABLE [ThreadMessage] CHECK CONSTRAINT [FK_ThreadMessage_ForumThread1]

    GO

    -----------------------------------------------

    INSERT INTO [ForumThread]

    ([ThreadSection],[OpenDate],[CloseDate],[Deleted]) VALUES (1,'1/1/2011','2/2/2012',0)

    GO

    INSERT INTO [ForumThread]

    ([ThreadSection],[OpenDate],[CloseDate],[Deleted]) VALUES (1,'1/10/2011','2/10/2012',0)

    GO

    ------------------------------------

    INSERT INTO [ThreadMessage]([MessageSender],[MessageThread],[MessageParent],[MessageTitle] ,[MessageDate],[MessageContent],[UpdateDate],[Deleted])

    VALUES (1,1,0,'Introduction','1/1/2012','Please Introduce yourself to the class','1/1/2012',0)

    GO

    INSERT INTO [ThreadMessage]([MessageSender],[MessageThread],[MessageParent],[MessageTitle] ,[MessageDate],[MessageContent],[UpdateDate],[Deleted])

    VALUES (2,1,1,'RE:Introduction','1/2/2012','My Name is Mark. I am interested in sports.','1/2/2012',0)

    GO

    INSERT INTO [ThreadMessage]([MessageSender],[MessageThread],[MessageParent],[MessageTitle] ,[MessageDate],[MessageContent],[UpdateDate],[Deleted])

    VALUES (3,1,1,'RE:Introduction','1/2/2012','My Name is Sou. I am interested in music.','1/2/2012',0)

    GO

    INSERT INTO [ThreadMessage]([MessageSender],[MessageThread],[MessageParent],[MessageTitle] ,[MessageDate],[MessageContent],[UpdateDate],[Deleted])

    VALUES (4,1,2,'RE:Introduction','1/3/2012','My Name is Sam. Mark I think we met before. I am also interested in sports.','1/2/2012',0)

    GO

    ------------------------------------

    INSERT INTO [ThreadMessage]([MessageSender],[MessageThread],[MessageParent],[MessageTitle] ,[MessageDate],[MessageContent],[UpdateDate],[Deleted])

    VALUES (1,2,0,'Chapter1','1/10/2012','Mention What you benefit from chapter1 ','1/10/2012',0)

    GO

    Thank you

  • March 19, 2012 at 3:10 am

    #1461029

    Using recursive CTEs

    WITH Recur AS (

    SELECT MessageThread,MessageContent,MessageID,MessageDate

    FROM ThreadMessage

    WHERE MessageParent=0

    UNION ALL

    SELECT r.MessageThread,r.MessageContent,m.MessageID,m.MessageDate

    FROM ThreadMessage m

    INNER JOIN Recur r ON r.MessageID=m.MessageParent

    )

    SELECT MessageThread AS ThreadID,

    MessageContent AS MessageTitle,

    COUNT(*) AS NumberOfReplies,

    MAX(MessageDate) AS LastPostDate

    FROM Recur

    GROUP BY MessageThread,MessageContent

    ORDER BY MessageThread;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply