SQL query question.

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.

Re: SQL query question.

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

Re: SQL query question.

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

Re: SQL query question.

Roy Harvey (SQL Server MVP) wrote:

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

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.

Re: SQL query question.

thanks for the help people

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