|
|
Posted by danny on 11/02/07 04:57
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.
Navigation:
[Reply to this message]
|