Posted by Roy Harvey (SQL Server MVP) on 11/01/07 12:06

What is causing the problem is

>(BLOCKED_USERS.blocked_email <> 'bob@a.com')

This turns the LEFT OUTER join into an INNER join by eliminating all
the NULL BLOCKED_USERS as the NULLs fail this test.

One fix is to move this test to the ON clause of the LEFT OUTER JOIN
with an AND. Another is to use NOT EXISTS instead.

SELECT A.email
FROM USERS as A
WHERE NOT EXISTS
(SELECT *
FROM BLOCKED_USERS as B
WHERE A.email = B.user_email)
AND A.email <> 'bob@a.com'

Roy Harvey
Beacon Falls, CT

On Thu, 01 Nov 2007 02:03:15 -0400, danny <teddy@woh.rr.com> wrote:

>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]