August 17, 2011 at 7:43 am
Hello Everyone,
I have a situation whereby I have a MessagingThreads table, a Messages table and a ParentMessages table, as described in the SQL script below. The ParentMessages table refers back to the Messages table whereby a message can be the parent of one or more "child" messages, and a message can be the child of one or more "parent" messages.
In order to explain this better, here's a visual representation:
Message Thread 1
Message 1
--Message 2 (child of msg 1)
--Message 3 (child of msg 1)
----Message 4 (child of msg 3)
------Message 5 (child of msg 5)
------Message 6 (child of msg 5)
Message Thread 2
Message 7
--Message 1 (child of msg 7)
----Message 2 (child of msg 1)
----Message 3 (child of msg 1)
------Message 4 (child of msg 3)
--------Message 5 (child of msg 5)
--------Message 6 (child of msg 5)
My question, is how can I retrieve this using CTE (which I "think" I will need to use)?
Any help is always gratefully received!
Here's the generation script:
IF OBJECT_ID('tempdb..#MessageThreads') IS NOT NULL
DROP TABLE #MessageThreads
GO
IF OBJECT_ID('tempdb..#Messages') IS NOT NULL
DROP TABLE #Messages
GO
IF OBJECT_ID('tempdb..#ParentMessages') IS NOT NULL
DROP TABLE #ParentMessages
GO
CREATE TABLE #MessageThreads (
ThreadID INT NOT NULL IDENTITY(1,1)
,ThreadSubject VARCHAR(100)
,CONSTRAINT [PK_MessageThreads] PRIMARY KEY CLUSTERED
(
[ThreadID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE #Messages(
[MessageID] [bigint] IDENTITY(1,1) NOT NULL
,[ThreadID] [bigint] NOT NULL
,[MessageText] [varchar](max) NOT NULL
,CONSTRAINT [PK_UserMessages] PRIMARY KEY CLUSTERED
(
[MessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE #ParentMessages(
[ParentMessageID] [bigint] NOT NULL,
[MessageID] [bigint] NOT NULL,
CONSTRAINT [PK_UserParentMessages] PRIMARY KEY CLUSTERED
(
[ParentMessageID] ASC,
[MessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO #MessageThreads (ThreadSubject)
SELECT 'Thread 1'
UNION
SELECT 'Thread 2'
UNION
SELECT 'Thread 3'
SELECT * FROM #MessageThreads
INSERT INTO #Messages (ThreadID,MessageText)
SELECT 1,'Message 1, Thread 1'
UNION
SELECT 1,'Message 2, Thread 1'
UNION
SELECT 2,'Message 3, Thread 2'
UNION
SELECT 2,'Message 4, Thread 2'
UNION
SELECT 2,'Message 5, Thread 2'
UNION
SELECT 3,'Message 6, Thread 3'
SELECT * FROM #Messages
INSERT INTO #ParentMessages(ParentMessageID,MessageID)
SELECT 3,1
UNION
SELECT 3,2
UNION
SELECT 6,3
UNION
SELECT 6,4
SELECT * FROM #ParentMessages
Many thanks, btw!!
August 17, 2011 at 8:46 am
This should get you started.
DECLARE @Thread BIGINT
SET @Thread = 1
;WITH CTE_Message
AS
(
SELECT
'ParentMessageID' = CAST(MessageId as BIGINT)
, 'MessageId' = CAST(NULL as BIGINT)
FROM #MessageThreads
JOIN #Messages ON #MessageThreads.ThreadID = #Messages.ThreadID
WHERE #MessageThreads.ThreadID = @Thread
UNION ALL
SELECT
#ParentMessages.ParentMessageID
, #ParentMessages.MessageId
FROM #ParentMessages
INNER JOIN CTE_Message
ON #ParentMessages.MessageID = CTE_Message.ParentMessageID
)
SELECT *
FROM CTE_Message
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply