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 me out with a better SQL.

On 1 Nov, 06:07, danny <teddy@woh.rr.com> 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 me out with a better SQL.

untested (but sounds like what you're after to me)

SELECT u.* FROM blocked_users b
LEFT JOIN users u ON u.email = b.email
WHERE b.blocked_email = 'bob@a.com'
AND u.email IS NULL
AND e.email <> 'bob@a.com';