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

TABLE: BLOCKED_USERS
---------------------------------------

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.

What is causing the problem is

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 (AT) woh (DOT) rr.com> wrote:

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 (AT) woh (DOT) rr.com> wrote

Roy Harvey (SQL Server MVP) wrote:

But they'll pass the other part of the test:

OR
(BLOCKED_USERS.blocked_email IS NULL)

That said, I agree that NOT EXISTS is the right way to go.

thanks for the help people

danny wrote: