Web Analytics Made Easy -
StatCounter need help with left join - CodingForum

Announcement

Collapse
No announcement yet.

need help with left join

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • need help with left join

    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?

  • #2
    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)

    Comment

    Working...
    X