Posted by Dan Guzman on 11/01/07 12:37

> I know what's causing the two issues and it's the JOIN. Would someone
> PLEASE help me out with a better SQL.

I suggest you use NOT EXISTS instead of LEFT OUTER JOIN for your
requirements. This will ensure you don't get the same user back multiple
times in cases where a user has blocked multiple users. Below is a sample
proc:

CREATE PROCEDURE dbo.GetUserList
@user_email varchar(255)
AS
SET NOCOUNT ON

SELECT
USERS.email
FROM dbo.USERS
WHERE
USERS.email <> @user_email --exclude self
AND NOT EXISTS --exclude blocking users
(
SELECT *
FROM dbo.BLOCKED_USERS
WHERE
USERS.email = BLOCKED_USERS.user_email
AND BLOCKED_USERS.blocked_email = @user_email
)

RETURN @@ERROR
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"danny" <teddy@woh.rr.com> wrote in message
news:47296c49$0$32563$4c368faf@roadrunner.com...
> Hate to ask trivial questions on newsgroup but I've had no luck on google,
> IRC, or a 500 page SQL book. Long story short I'm creating a instant
> messaging web site that allows people to block messages from specific
> users (spammers, etc)
>
> Here's what the 2 tables look like
>
> TABLE: USERS
> ---------------------------------------
> | USER EMAIL | USER NAME, ETC...
> | dave@a.com |
> | candy@a.com |
> | bob@a.com |
> | sherry@a.com|
> ---------------------------------------
>
> TABLE: BLOCKED_USERS
> ---------------------------------------
> | USER EMAIL | BLOCKED_EMAIL
> | dave@a.com | bob@a.com
> | candy@a.com | dave@a.com
> | candy@a.com | bob@a.com
> ---------------------------------------
>
> Mind you when the user fires off a message their EMAIL address is passed
> off to the stored procedure (which I'm having the trouble on). User Bob
> sends out a message - calling the following SQL (HENCE - his email address
> is in the WHERE clause to find out if he has anyone blocking him. And get
> a list of email address of all the recipients who are NOT blocking Bob).
>
> *******************************************************
> SELECT USERS.email
> FROM USERS
> LEFT OUTER JOIN
> BLOCKED_USERS ON USERS.email = BLOCKED_USERS.user_email
> WHERE
> (BLOCKED_USERS.blocked_email <> 'bob@a.com')
> OR
> (BLOCKED_USERS.blocked_email IS NULL)
> *******************************************************
>
> Firing the SQL produces...
>
> *******************************************************
> EMAIL
> --------------
> bob@a.com
> sherry@a.com
> candy@a.com
> *******************************************************
>
> This is the wrong results. First Bob would get a message to himself since
> he is not in the BLOCKED_USERS table (JOINed on BLOCKED_USERS and USERS in
> the column email address). Secondly Candy would receive Bob's message (but
> Candy has Bob on the BLOCK_USERS list).
>
> The correct result should return sherry@a.com ONLY.
>
> I know what's causing the two issues and it's the JOIN. Would someone
> PLEASE help me out with a better SQL.

[Back to original message]