SQL query question.

[danny]

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...
| da**@a.com |
| ca***@a.com |
| bo*@a.com |
| sh****@a.com|
---------------------------------------

TABLE: BLOCKED_USERS
---------------------------------------
| USER EMAIL | BLOCKED_EMAIL
| da**@a.com | bo*@a.com
| ca***@a.com | da**@a.com
| ca***@a.com | bo*@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 <'bo*@a.com')
OR
(BLOCKED_USERS.blocked_email IS NULL)
************************************************** *****

Firing the SQL produces...

************************************************** *****
EMAIL
--------------
bo*@a.com
sh****@a.com
ca***@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 sh****@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.

[Roy Harvey (SQL Server MVP)]

What is causing the problem is

>(BLOCKED_USERS.blocked_email <'b**@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 <'b**@a.com'

Roy Harvey
Beacon Falls, CT

On Thu, 01 Nov 2007 02:03:15 -0400, danny <te***@woh.rr.comwrote:

>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...
| da**@a.com |
| ca***@a.com |
| bo*@a.com |
| sh****@a.com|
---------------------------------------

TABLE: BLOCKED_USERS
---------------------------------------
| USER EMAIL | BLOCKED_EMAIL
| da**@a.com | bo*@a.com
| ca***@a.com | da**@a.com
| ca***@a.com | bo*@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 <'b**@a.com')
OR
(BLOCKED_USERS.blocked_email IS NULL)
************************************************* ******

Firing the SQL produces...

************************************************* ******
EMAIL
--------------
bo*@a.com
sh****@a.com
ca***@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 sh****@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]

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" <te***@woh.rr.comwrote in message
news:47***********************@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...
| da**@a.com |
| ca***@a.com |
| bo*@a.com |
| sh****@a.com|
---------------------------------------

TABLE: BLOCKED_USERS
---------------------------------------
| USER EMAIL | BLOCKED_EMAIL
| da**@a.com | bo*@a.com
| ca***@a.com | da**@a.com
| ca***@a.com | bo*@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 <'bo*@a.com')
OR
(BLOCKED_USERS.blocked_email IS NULL)
************************************************** *****

Firing the SQL produces...

************************************************** *****
EMAIL
--------------
bo*@a.com
sh****@a.com
ca***@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 sh****@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]

Roy Harvey (SQL Server MVP) wrote:

What is causing the problem is

>(BLOCKED_USERS.blocked_email <'b**@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]

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...
| da**@a.com |
| ca***@a.com |
| bo*@a.com |
| sh****@a.com|
---------------------------------------

TABLE: BLOCKED_USERS
---------------------------------------
| USER EMAIL | BLOCKED_EMAIL
| da**@a.com | bo*@a.com
| ca***@a.com | da**@a.com
| ca***@a.com | bo*@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 <'bo*@a.com')
OR
(BLOCKED_USERS.blocked_email IS NULL)
************************************************** *****

Firing the SQL produces...

************************************************** *****
EMAIL
--------------
bo*@a.com
sh****@a.com
ca***@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 sh****@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.