Posted by danny on 11/01/07 06:03

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]