SQL query question.

**Roy Harvey (SQL Server MVP)** · Nov 1 '07, 12:15 PM

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 <teddy@woh.rr.c omwrote:

>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_USER S ON USERS.email = BLOCKED_USERS.u ser_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.

**Dan Guzman** · Nov 1 '07, 12:45 PM

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_USE RS
WHERE
USERS.email = BLOCKED_USERS.u ser_email
AND BLOCKED_USERS.b locked_email = @user_email
)

RETURN @@ERROR
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"danny" <teddy@woh.rr.c omwrote 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.u ser_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.

**Ed Murphy** · Nov 2 '07, 02:05 AM

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.

**danny** · Nov 2 '07, 05:05 AM

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

SQL query question.

SQL query question.

Comment

Comment

Comment

Comment