July 24, 2015 at 5:50 am
I have successfully built a messaging system into my application, I now in the process of displaying the messages in the UI.
The following are how my tables are constructed.
CREATE TABLE [MailBox].[Message](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SenderId] [bigint] NOT NULL,
[Message] [nvarchar](max) NOT NULL,
[SentDate] [datetime] NOT NULL,
CONSTRAINT [PK_MailBox.Message] 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]
CREATE TABLE [MailBox].[MessageRecipient](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[MessageId] [bigint] NOT NULL,
[RecipientId] [bigint] NOT NULL,
[ReadDate] [datetime] NULL,
[DeletedDate] [datetime] NULL,
CONSTRAINT [PK_MailBox.MessageRecipient] 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]
Now I haven't set the foreign key on the MessageReceipient table yet so please bare with. When someone sends me an email I insert a record into [MailBox].[Message] and output the insert id into MessageReceipient along with the ReceipientId this is working as expected, when I then click on my inbox I call the following stored procedure:
Select p.Username, count(mr.RecipientId) [TotalMessages],
CASE
WHEN mr.ReadDate is null then 1 -- New message
WHEN mr.ReadDate is not null then 0 -- Message has been read
END AS NewMessage
FROM [User].[User_Profile] p
JOIN [MailBox].[Message] m on p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId
GROUP BY p.Username, mr.RecipientId, mr.ReadDate
This will give me the person who has emailed me, the total amount of messages and if the message is new or its been read, I do this by checking the ReadDate column as shown in the case statement (but this gives me duplicates, which is not what I want). The issue I am experiencing / hoping a sql guru can help with is, Lets say user1 emails me 5 times so when I call this proc I will have the same user displayed to me 5 times, what I'm trying to achieve with the proc is it will show User1 as the following:
User1 5 Messages 1 or 0 New Messages
I can get it to display as follow when I remove the case statement
User1 5 Messages
but as soon as I add the case statement back in then I get 5 rows.
How can I change this proc in such a way that it will display the data as follows;
User1 5 Messages 1 or 0 New Messages
New Messages is dependent on ReadDate if its null then we have a new message, otherwise its been read
Any help would be great.
July 24, 2015 at 6:26 am
I've managed to get this to work by doing the following :
Select p.Username, count(mr.RecipientId) [TotalMessages],
(SELECT COUNT(*) FROM [MailBox].MessageRecipient where RecipientId = @userid and ReadDate is null) [NewMail]
FROM [User].[User_Profile] p
JOIN [MailBox].[Message] m on p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId
GROUP BY p.Username, mr.RecipientId
Ideally I would like to try and get this to work without using another select statement if possible?
July 24, 2015 at 6:38 am
SELECT
p.Username,
COUNT(mr.RecipientId) AS [TotalMessages],
x.NewMessage
FROM [User].[User_Profile] p
JOIN [MailBox].[Message] m ON p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr ON m.Id = mr.MessageId
CROSS APPLY (
SELECT [NewMessage] = CASE
WHEN mr.ReadDate IS NULL THEN 1 -- New message
ELSE 0 END-- Message has been read
) x
GROUP BY p.Username, mr.RecipientId, x.NewMessage
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 24, 2015 at 6:46 am
The problem is not your CASE statement, but the GROUP BY clause. You need to group the same way you're presenting the information, otherwise you'll get unexpected results with apparent duplicates.
--Version 1
Select p.Username,
count(mr.RecipientId) [TotalMessages],
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage
FROM [User].[User_Profile] p
JOIN [MailBox].[Message] m on p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId
GROUP BY p.Username,
mr.RecipientId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END;
--Version 2
WITH CTE(
Select p.Username,
mr.RecipientId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage
FROM [User].[User_Profile] p
JOIN [MailBox].[Message] m on p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId
)
SELECT Username,
count(RecipientId) [TotalMessages],
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage
FROM CTE
GROUP BY Username,
RecipientId,
NewMessage;
--Version 3 using crosstabs
Select p.Username,
count( mr.RecipientId) [TotalMessages],
count( CASE WHEN mr.ReadDate is null THEN mr.RecipientId END) [NewMessages],
count( CASE WHEN mr.ReadDate is NOT NULL THEN mr.RecipientId END) [ReadMessages]
FROM [User].[User_Profile] p
JOIN [MailBox].[Message] m on p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId
GROUP BY p.Username,
mr.RecipientId;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply