Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Question Unanswered: 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.

    What am I doing wrong and how can I fix my query?

  2. #2
    Join Date
    Feb 2004
    Location
    Siggenthal, Switzerland
    Posts
    19
    SELECT COUNT(er.user_id) as "count", e.sender_id, er.user_id, e.email_type
    FROM emails e, email_recipients er
    WHERE e.email_id = er.email_id
    AND (e.sender_id!=296 OR e.email_type!=1)
    GROUP BY e.sender_id, er.user_id
    HAVING count>=2 AND e.sender_id=296

    Walter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •