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?
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?
Comment