need help with left join

[achieve]

Hi,

I'm not getting the results I'm expecting when I'm doing a left join.

Here are my tables:

CREATE TABLE users (
user_id int(8) NOT NULL auto_increment,
name varchar(100) NOT NULL,
PRIMARY KEY (user_id)
);

INSERT INTO users VALUES ( '84917', 'Harry Potter');
INSERT INTO users VALUES ( '296', 'George Bush');



CREATE TABLE emails (
email_id int(8) NOT NULL auto_increment,
sender_id int(40) DEFAULT '0' NOT NULL,
subject varchar(255) NOT NULL,
body blob NOT NULL,
email_type int(8) DEFAULT '0' NOT NULL,
PRIMARY KEY (email_id)
);

INSERT INTO emails VALUES ( '308598', '296', 'Great to hear from you!', 'Dear Harry, Thanks for writing!', '1');
INSERT INTO emails VALUES ( '307583', '84917', 'Dear President', 'How are you doing?', '0');
INSERT INTO emails VALUES ( '305411', '84917', 'Hi', 'Hey George Bush', '0');
INSERT INTO emails VALUES ( '306787', '296', 'Hey Harry', 'Keep those e-mails coming.', '0');


CREATE TABLE email_recipients (
user_id int(40) DEFAULT '0' NOT NULL,
email_id int(8) DEFAULT '0' NOT NULL
);

INSERT INTO email_recipients VALUES ( '84917', '306787');
INSERT INTO email_recipients VALUES ( '296', '305411');
INSERT INTO email_recipients VALUES ( '296', '307583');
INSERT INTO email_recipients VALUES ( '84917', '308598');

I'm trying to get a list of users that sent at least 2 e-mails to George Bush and did NOT receive an e-mail from George Bush where email_type is 1.

Here is my query:

SELECT users.user_id, users.name,
COUNT(distinct e1.email_id) as email_count
FROM emails e1, email_recipients r1, users
LEFT JOIN email_recipients r2 ON (users.user_id = r2.user_id)
LEFT JOIN emails e2 ON
(e2.email_id = r2.email_id
AND e2.sender_id = 296
AND e2.email_type = 1)
WHERE e1.sender_id = users.user_id
AND e1.email_id = r1.email_id
AND r1.user_id = 296
AND e2.email_id IS NULL
GROUP BY users.user_id
HAVING email_count >= 2;

I shouldn't be getting back any rows because Harry Potter DID recieve an e-mail of email_type 1 from George Bush.

But I'm getting back Harry Potter.

This is happening because of the the e-mail that George Bush sent that is NOT email_type 1. when I remove that record (email_id = 306787 ) from the database, then I get no results which is what I want.

Why isn't my query working?

[markhartnady]

I dont know but its probably got something to do with this part of your join statement which doesnt really make sense:

(e2.email_id = r2.email_id
AND e2.sender_id = 296
AND e2.email_type = 1)