discussion forum thread,message,no of replies

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

discussion forum thread,message,no of replies

Author

Topic

Nadermfr
Starting Member

4 Posts

Posted - 2012-03-17 : 19:35:11

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-18 : 13:02:37

use common table expressions


declare @ThreadID int
set @ThreadID = 1 --just an example value
;With msgtemp
AS
(
select ft.threadid, tm.messagetitle,tm.messagecontent,cast(1 as int) as numberofreplies,messagedate,tm.MessageID
from [ForumThread] ft
inner join [ThreadMessage] tm
ON ft.ThreadID = tm.MessageThread
where tm.MessageParent = 0
and ft.ThreadID = @ThreadID

union all

select tm.MessageThread, tm.messagetitle,tm.messagecontent,1,tm.messagedate,tm.MessageID
from msgtemp t
--inner join [ForumThread] ft
-- on ft.ThreadID = t.ThreadID
inner join [ThreadMessage] tm
ON t.ThreadID = tm.MessageThread
and t.MessageID = tm.MessageParent

)

SELECT *
FROM msgtemp
option (maxrecursion 0)

output
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
threadid	messagetitle	messagecontent	                                                             numberofreplies	messagedate	                  MessageID
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1	        Introduction	Please Introduce yourself to the class	                                     1	                2012-01-01 00:00:00.000	           1
1	        RE:Introduction	My Name is Mark. I am interested in sports.                                  1	                2012-01-02 00:00:00.000	           2
1	        RE:Introduction	My Name is Sou. I am interested in music.                                    1	                2012-01-02 00:00:00.000	           3
1	        RE:Introduction	My Name is Sam. Mark I think we met before. I am also interested in sports.  1	                2012-01-03 00:00:00.000	           4

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-18 : 13:06:20

for count just use count() to get count of articles. i dont know how you will display the titles as there are multiple titles involved.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-03-18 : 16:27:08

Thank you for your reply.
We need to display only threadid and its main message title.
that is only threadid , messagetitle for those with parentmessage=0 but at the same time find number of replies and max messagedate for all messages with threadmessage = to that thread.
Thank you again

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-18 : 16:36:14

then change the code to


declare @ThreadID int
set @ThreadID = 1 --just an example value

;With msgtemp
AS
(
select ft.threadid, tm.messagetitle,tm.messagecontent,cast(1 as int) as numberofreplies,messagedate,tm.MessageID,tm.MessageParent 
from [ForumThread] ft
inner join [ThreadMessage] tm
ON ft.ThreadID = tm.MessageThread
where tm.MessageParent = 0
and ft.ThreadID = @ThreadID

union all

select tm.MessageThread, tm.messagetitle,tm.messagecontent,1,tm.messagedate,tm.MessageID,tm.MessageParent 
from msgtemp t
--inner join [ForumThread] ft
-- on ft.ThreadID = t.ThreadID
inner join [ThreadMessage] tm
ON t.ThreadID = tm.MessageThread
and t.MessageID = tm.MessageParent

)

select threadid,
count(MessageID) AS ReplyCount,
max(MessageDate) AS LatestMEssageDate,
MAX(case when MessageParent = 0 then messagetitle end) as ParentTitle
from msgtemp
group by threadid

option (maxrecursion 0)



output
--------------------------------------------------------------------------------------------------------
threadid	ReplyCount	LatestMEssageDate	                ParentTitle
--------------------------------------------------------------------------------------------------------
1	        4	        2012-01-03 00:00:00.000	                Introduction

change the @ThreadID accordingly to get associated info

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources