September 27, 2010 at 3:26 pm
Hi,
I'm trying to create a Thread View for a messaging system.
I have 1 Table called mt_messages that houses both the sent and received messages for all users. Below is the table.
USE [mtdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mt_messages](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[msgFrom] [uniqueidentifier] NOT NULL,
[msgTo] [uniqueidentifier] NOT NULL,
[msgRead] [numeric](1, 0) NOT NULL,
[msgDate] [datetime] NOT NULL,
[msgSubject] [varchar](50) NOT NULL,
[msgContent] [varchar](2500) NOT NULL,
[threadID] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[mt_messages] ADD CONSTRAINT [DF__mt_message__read__3335971A] DEFAULT ((0)) FOR [msgRead]
GO
In this table the threadID will be the same for all messages sent or received on the same topic.
For EX. If UserA send a message to UserB and UserB replies to that message then both messages will have the Same ThreadID. however if UserB send a reply via the New Message window the the threadID will be different.
When UserA goes to the messages page I need to show the user only the 1st message in the thread. This message has the link to showdetails page that will show the entire thread.
How do i achieve this in SQL.
My Goal: Pull only those records where ThreadID is unique and where MsgTo is equal to the Logged In Users GUID. (these can be multiple rows)
I'm not sure if i'm going about this in the Right Way! Can this be achieved using 1 table or do i need to use 2/3 tables?
Any suggestions?
Thanks in Advance 🙂
Johnfern
John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC
September 27, 2010 at 4:34 pm
This example has not been tested, but this should give you the first message for each threads for a specific To-User. This should at least get you started in the right direction.
DECLARE @msgToUser uniqueidentifier
SET@msgToUser = 'some value'
SELECT t.*
FROMdbo.mt_messages t
INNER JOIN (
SELECT threadID,
MIN(msgDate) as FirstMessage
FROMdbo.mt_messages
GROUP BY threadID
) t2 on t.threadID = t2.threadID AND t.msgDate = t2.FirstMessage
WHEREt.msgTo = @msgToUser
September 28, 2010 at 4:41 am
John Thanks for the reply however i could not get this to work.
here is some dummy data
IDmsgFrommsgTomsgReadmsgDatemsgSubjectmsgContentthreadID
1DB50748B-C86E-42F7-AA31-3D48DEBB1BFE431D7BA3-2D31-4422-B8DE-91D6F922A53E02010-09-27 02:02:19.820Sub 1Msg 11
2DB50748B-C86E-42F7-AA31-3D48DEBB1BFE431D7BA3-2D31-4422-B8DE-91D6F922A53E02010-09-27 02:08:15.693Sub 2Msg 22
3DB50748B-C86E-42F7-AA31-3D48DEBB1BFE431D7BA3-2D31-4422-B8DE-91D6F922A53E02010-09-27 02:09:07.780Sub 3Msg 33
4431D7BA3-2D31-4422-B8DE-91D6F922A53EDB50748B-C86E-42F7-AA31-3D48DEBB1BFE02010-09-27 02:54:45.150Sub 1Msg 41
5431D7BA3-2D31-4422-B8DE-91D6F922A53EDB50748B-C86E-42F7-AA31-3D48DEBB1BFE02010-09-27 02:55:25.920Sub 1Msg 51
6431D7BA3-2D31-4422-B8DE-91D6F922A53EDB50748B-C86E-42F7-AA31-3D48DEBB1BFE02010-09-27 03:03:11.517Sub 1Msg 61
7431D7BA3-2D31-4422-B8DE-91D6F922A53EDB50748B-C86E-42F7-AA31-3D48DEBB1BFE02010-09-27 03:06:20.673Sub 1Msg 71
8431D7BA3-2D31-4422-B8DE-91D6F922A53EDB50748B-C86E-42F7-AA31-3D48DEBB1BFE02010-09-27 03:07:22.417Sub 1Msg 81
So the output i hope to get will be Rows with ID 1,2,3 as these have unique ThreadIDs for the User DB50748B-C86E-42F7-AA31-3D48DEBB1BFE
Is this possible or will it be better for me to use 3 Tables.
I was thinking something like
mt_msgThreads
ID, msgSubject, msgRead
mt_messages
ID, msgFrom, msgContent, threadID
mt_usersInThread
ID, msgTo, threadID
where mt_messsages.threadID will be mt_msgThreads.ID and mt_usersInThread.threadID will be mtmsgThreads.ID
Any suggestions?
GOAL: Private message system that will allow 1 user to send a message to a single/multiple users. The receiving User(s) can reply to the same thread.
Thanks
Johnfern
John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC
September 28, 2010 at 5:11 am
You can use ROW_NUMBER() in a CTE to select the Id's from the first post and filter on the row number.
DECLARE @T TABLE (
[ID] [numeric](18, 0) NOT NULL,
[msgFrom] [uniqueidentifier] NOT NULL,
[msgTo] [uniqueidentifier] NOT NULL,
[msgRead] [numeric](1, 0) NOT NULL,
[msgDate] [datetime] NOT NULL,
[msgSubject] [varchar](50) NOT NULL,
[msgContent] [varchar](2500) NOT NULL,
[threadID] [numeric](18, 0) NULL
)
INSERT @T
SELECT 1, 'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE', '431D7BA3-2D31-4422-B8DE-91D6F922A53E', 0, '2010-09-27 02:02:19.820', 'Sub 1', 'Msg 1', 1
UNION SELECT 2, 'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE', '431D7BA3-2D31-4422-B8DE-91D6F922A53E', 0, '2010-09-27 02:08:15.693', 'Sub 2', 'Msg 2', 2
UNION SELECT 3, 'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE', '431D7BA3-2D31-4422-B8DE-91D6F922A53E', 0, '2010-09-27 02:09:07.780', 'Sub 3', 'Msg 3', 3
UNION SELECT 4, '431D7BA3-2D31-4422-B8DE-91D6F922A53E', 'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE', 0, '2010-09-27 02:54:45.150', 'Sub 1', 'Msg 4', 1
UNION SELECT 5, '431D7BA3-2D31-4422-B8DE-91D6F922A53E', 'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE', 0, '2010-09-27 02:55:25.920', 'Sub 1', 'Msg 5 ', 1
UNION SELECT 6, '431D7BA3-2D31-4422-B8DE-91D6F922A53E', 'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE', 0, '2010-09-27 03:03:11.517', 'Sub 1', 'Msg 6', 1
UNION SELECT 7, '431D7BA3-2D31-4422-B8DE-91D6F922A53E', 'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE', 0, '2010-09-27 03:06:20.673', 'Sub 1', 'Msg 7', 1
UNION SELECT 8, '431D7BA3-2D31-4422-B8DE-91D6F922A53E', 'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE', 0, '2010-09-27 03:07:22.417' , 'Sub 1', 'Msg 8 ', 1;
WITH A (Id, Row) AS (
SELECT Id,
Row_Number() OVER (PARTITION BY ThreadId ORDER BY msgDate)
FROM@T
)
SELECTT.*
FROM@T T INNER JOIN
A ON T.Id = A.Id
WHERERow = 1
September 29, 2010 at 1:57 am
Louis thanks for the response. This works very well.
John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC
September 29, 2010 at 2:20 am
Hi,
Using the suggestions by Louis i made changes in my code and my db structure. this is what i came up with after some thinking.
i now have 3 Tables [below]
mtt_threads
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mtt_threads](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[subject] [varchar](100) NULL,
CONSTRAINT [PK_mtt_threads] PRIMARY KEY CLUSTERED
(
[ID] 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
SET ANSI_PADDING OFF
GO
mtt_threads_content (stores the messages)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mtt_threads_content](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[threadID] [numeric](18, 0) NOT NULL,
[msgContent] [varchar](2000) NULL,
[msgData] [datetime] NOT NULL,
[authorID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[mtt_threads_content] WITH CHECK ADD CONSTRAINT [FK_mtt_threads_content_mtt_threads] FOREIGN KEY([threadID])
REFERENCES [dbo].[mtt_threads] ([ID])
GO
ALTER TABLE [dbo].[mtt_threads_content] CHECK CONSTRAINT [FK_mtt_threads_content_mtt_threads]
GO
mtt_threads_users (hold the receivers of the message)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mtt_threads_users](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[threadID] [numeric](18, 0) NOT NULL,
[userID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[mtt_threads_users] WITH CHECK ADD CONSTRAINT [FK_mtt_threads_users_mtt_threads] FOREIGN KEY([threadID])
REFERENCES [dbo].[mtt_threads] ([ID])
GO
ALTER TABLE [dbo].[mtt_threads_users] CHECK CONSTRAINT [FK_mtt_threads_users_mtt_threads]
GO
I used the following test data
INSERT INTO mtt_threads (subject) VALUES ('Test Subject')
INSERT INTO mtt_threads (subject) VALUES ('Test Subject1')
INSERT INTO mtt_threads_content (threadID,msgContent,msgdata,authorID) values (1,'Test Content',GETDATE(),'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE')
INSERT INTO mtt_threads_content (threadID,msgContent,msgdata,authorID) values (2,'Test Content1',GETDATE(),'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE')
INSERT INTO mtt_threads_content (threadID,msgContent,msgdata,authorID) values (1,'Test Content - Reply',GETDATE(),'431D7BA3-2D31-4422-B8DE-91D6F922A53E')
INSERT INTO mtt_threads_content (threadID,msgContent,msgdata,authorID) values (1,'Test Content - Reply 1',GETDATE(),'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE')
INSERT INTO mtt_threads_users (threadID, userID) VALUES (1,'431D7BA3-2D31-4422-B8DE-91D6F922A53E')
INSERT INTO mtt_threads_users (threadID, userID) VALUES (2,'431D7BA3-2D31-4422-B8DE-91D6F922A53E')
INSERT INTO mtt_threads_users (threadID, userID) VALUES (1,'DB50748B-C86E-42F7-AA31-3D48DEBB1BFE')
INSERT INTO mtt_threads_users (threadID, userID) VALUES (1,'431D7BA3-2D31-4422-B8DE-91D6F922A53E')
I compiled the following SQL Query.
WITH A (threadID, content, Author, msgDate, Row) AS (
SELECT threadID, msgContent, authorID, msgData,
Row_Number() OVER (PARTITION BY ThreadId ORDER BY ID DESC)
FROM mtt_threads_content
)
select DISTINCT tu.threadID, t.subject, A.Author, A.msgDate, A.content from mtt_threads_users tu,mtt_threads t inner join A on t.id=A.threadID where tu.userID = '431D7BA3-2D31-4422-B8DE-91D6F922A53E' and t.id=tu.threadID and Row=1
Please could this be veified and let me know if i'm on the right track. I think i am
Thanks.
Johnfern
John P Fernandes
Enterprise Premier Support | Networking | Microsoft India GTSC
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply