July 25, 2015 at 6:21 am
I have the following two tables:
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]
I'm building a messaging functionality in to my application, I'm able to insert a message into the database and this message then appears inside the other users inbox. The issue I have it when I click on this message to view the conversation I make a call to the following sp as shown here:
@userid bigint,
@SenderId bigint
AS
BEGIN
SET NOCOUNT ON;
Select m.Id, p.Id [SenderId], mr.RecipientId, p.Username, m.[Message], p.LastLoggedIn, p.LoggedIn, up.PhotoId
FROM [User].[User_Profile] p
JOIN [User].[User_Photos] up on p.Id = up.UserId
JOIN [MailBox].[Message] m on p.Id = m.SenderId
JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId
where
(up.UserId = @userid or up.UserId = @SenderId)
and
up.IsProfilePic = 1
and
(m.SenderId = @SenderId and mr.RecipientId = @userid)
or
(m.SenderId = @userid and mr.RecipientId = @SenderId)
order by m.Id
The problem with this is I'm trying to connect to the user photos table to return their profile picture, but for some reason even though I have specified IsProfilePic I get all the photos returned, instead it should be two photos, one for the @userid and the other for the @SenderId, its equivalent to me doing this:
Select *
From [User].[User_Photos]
where (UserId = 1 or UserId = 2) and IsProfilePic = 1
And this returns me the correct information. So, I'm unsure where I have gone wrong, any help would be appreciated.
July 25, 2015 at 6:36 am
July 25, 2015 at 6:46 am
July 25, 2015 at 7:27 pm
.Netter (7/25/2015)
I've just managed to get resolve this, I changed my where statement to look like the following:
where
(p.id = @userid or p.id = @SenderId)
and
up.IsProfilePic = 1
and (mr.RecipientId = @userid and m.SenderId = @SenderId or m.SenderId = @userid and mr.RecipientId = @SenderId)
And it worked as expected.
Are you sure? If the intent of the final AND grouping is what I think it is (two separate pairs of ANDs where either pair could be true) then you need to write it like this to be absolutely correct because OR is NOT a Boolean grouping function like AND is. Note the extra set of parentheses.
WHERE (p.id = @userid OR p.id = @SenderId)
AND up.IsProfilePic = 1
AND (
(mr.RecipientId = @userid AND m.SenderId = @SenderId)
OR (m.SenderId = @userid AND mr.RecipientId = @SenderId)
)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply