CODEWITHSUNDEEP
sql-serversql-server-2008
Code Ratchet
Code Ratchet

Reputation: 6029

Returning rows by users that aren't blocked sql server

I'm currently implementing blocking functionality into my web application. The user will have the option to block other users.

My current table set up is as follows:

CREATE TABLE [User].[Block_List]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint] NOT NULL, -- Person that I have blocked
    [BlockedById] [bigint] NOT NULL, -- Person who requested the block
    [DateBlocked] [datetime] NOT NULL,
    [DateUnblocked] [datetime] NULL
)

Now inside my stored procedure I'm joining this table to filter out the people that I've blocked as shown here:

 SELECT 
     p.Id [SenderId], 
     p.Username, 
     COUNT(mr.RecipientId) [TotalMessages], 
     COUNT(CASE WHEN mr.ReadDate IS NULL THEN 1 END) AS [NewMessages],
     up.PhotoId, 
     p.LastLoggedIn, 
     p.LoggedIn,
     MAX(m.SentDate)[EmailDate]
 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 AND up.isProfilePic = 1
 LEFT JOIN 
     [User].[Block_List] b ON p.Id = b.UserId
 WHERE 
     mr.RecipientId = 1 --and mr.DeletedDate is null
     AND (b.BlockedById = 1 and p.Id != b.UserId) -- Checking blocked table
 GROUP BY 
     p.id, p.Username, mr.RecipientId, up.PhotoId, p.LastLoggedIn, p.LoggedIn

I have hard coded the BlockedbyId to 1 as that's my current Id, unfortunately this doesn't return any rows when I would expect it to return at least one row, reason being I have two records and I've only blocked one of them.

When I execute the stored procedure above without the blocking where clause I get the following:

enter image description here

In my blocked table I have blocked the user with the Id of 2 as shown here:

enter image description here

When I add the blocking where clause back in I would expect to see the record for UserId 21 but unfortunately I don't get any records returned.

Any help would be great

Upvotes: 2

Views: 40

Answers (2)

maraca
maraca

Reputation: 8743

The following part

LEFT JOIN [User].[Block_List] b on p.Id = b.UserId
WHERE mr.RecipientId = 1 --and mr.DeletedDate is null
AND (b.BlockedById = 1 and p.Id != b.UserId) -- Checking blocked table

should be replaced by

WHERE mr.RecipientId = 1
AND m.SenderId NOT IN (SELECT UserId FROM [User].[Block_List] WHERE BlockedById = 1)

Especially if you want to count I would be very careful when joining a table, because you might end up having too many combinations if not correctly filtered out afterwards, using the where clause when possible is safer in my opinion except if you really want to join and use the values.

Upvotes: 1

DavidG
DavidG

Reputation: 118957

You have some mutually exclusive conditions set here. Your join says 

p.Id = b.UserId

However the where clause also says 

p.Id != b.UserId

So when you combine them you will get no rows.

Upvotes: 1

Related Questions