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

Question

Post reply

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

Sarsoura

Ten Centuries

Points: 1220
More actions
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

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

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

Mark Cowne One Orange Chip Points: 26968 More actions · Answer 1

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