July 26, 2015 at 1:22 am
I have the following query:
Select p.Id [SenderId], p.Username, up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn, p.LoggedIn
FROM [User].[User_Profile] p
JOIN [MailBox].[Message] m on p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId
LEFT JOIN [User].[User_Photos] up on p.Id = up.UserId
where mr.RecipientId = @userid and mr.DeletedDate is null
GROUP BY p.id, p.Username, mr.RecipientId, up.PhotoId,
p.LastLoggedIn, p.LoggedIn,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END;
The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?
So instead of showing this:
2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370
I'm trying to get it to look like this:
2Person112015-07-26 17:07:24.9370
Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.
2Person102015-07-26 17:07:24.9370
July 26, 2015 at 4:22 am
Your result set shows two different photo id's for the same user id. Try using APPLY to return a single row from the photos table, like this:
SELECT
p.Id [SenderId],
p.Username,
up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn,
p.LoggedIn
FROM [User].[User_Profile] p
JOIN [MailBox].[Message] m
on p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr
on m.Id = mr.MessageId
OUTER APPLY (
SELECT TOP 1 up.PhotoId
FROM [User].[User_Photos] up
WHERE p.Id = up.UserId ORDER BY up.PhotoId DESC
) x
WHERE mr.RecipientId = @userid
AND mr.DeletedDate IS NULL
GROUP BY p.id, p.Username, mr.RecipientId, up.PhotoId,
p.LastLoggedIn, p.LoggedIn,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END;
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply