title: JOIN LEFT with multiple conditions
body: <p>I'm having following tables structure</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `review_author` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    `email` varchar(255) NOT NULL,
    `client_id` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `IDX_37D99F0819EB6921` (`client_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2110 ;
  </code></pre>
  
  <p>AND </p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `brokers_comments` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `hb_broker_id` int(11) NOT NULL,
    `client_id` int(11) DEFAULT NULL,
    `user_name` varchar(100) NOT NULL,
    `user_email` varchar(100) NOT NULL,
    `state` int(11) NOT NULL DEFAULT '0',
    `text` varchar(3000) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `IDX_5365DFFB9FE55EF7` (`hb_broker_id`),
    KEY `IDX_5365DFFB19EB6921` (`client_id`),
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1583 ;
  </code></pre>
  
  <p>Before extracting value i did following query:</p>
  
  <pre><code> INSERT INTO review_author (
                      name,
                      email,
                      client_id
                  )
                  SELECT
                      brokers_comments.user_name,
                      brokers_comments.user_email,
                      brokers_comments.client_id
                  FROM brokers_comments
                  LEFT JOIN review_author
                  ON brokers_comments.user_name=review_author.name AND
                  brokers_comments.user_email=review_author.email AND
                  brokers_comments.client_id=review_author.client_id
                  WHERE  review_author.id  IS NULL
  </code></pre>
  
  <p>Not in review_author should be all author from table <code>brokers_comments</code> and now i'm trying to get authors id using following query:</p>
  
  <pre><code>SELECT
      review_author.id
  FROM brokers_comments
  LEFT JOIN review_author
      ON brokers_comments.user_name=review_author.name AND
      brokers_comments.user_email=review_author.email AND
      brokers_comments.client_id=review_author.client_id
  WHERE  review_author.id  IS NOT NULL
  </code></pre>
  
  <p>but i'm getting about 110 results from total 1531 records from table <code>brokers_comments</code>.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I couldn't manage to insert data in <a href="http://sqlfiddle.com/" rel="nofollow">http://sqlfiddle.com/</a> so following link are dump for two tables <em>review_author</em> and <em>brokers_comments</em>.</p>
  
  <p><em>Again my issue is to transfer distinct columns(user_name, user_email, client_id) from table brokers_comments to table review_author and then select review_author.id based on relation name/email/client_id from both tables.</em></p>
  
  <p><a href="http://wrttn.in/7ca325" rel="nofollow">http://wrttn.in/7ca325</a>
  <a href="http://wrttn.in/3a7885" rel="nofollow">http://wrttn.in/3a7885</a></p>
  
  <p>Insert new author was wrong and made duplication. Below is new correct form.</p>
  
  <pre><code>INSERT INTO review_author (
                      name,
                      email,
                      client_id
                  )
                  SELECT  user_name, user_email, client_id
                  FROM    brokers_comments AS broker
                  WHERE   NOT EXISTS
                          (
                          SELECT  1
                          FROM    review_author AS author
                          WHERE   author.email = broker.user_email
                          )
                  GROUP BY broker.user_email
  </code></pre>
  
  <p>P.S. I somebody will make a working online mysql database please put in comments so i could put it there.</p>
  
  <p><strong>Resolved</strong>
  Only now i realised that user_email must be unique. Based on this i made following select statement:</p>
  
  <pre><code>SELECT 
      author.id
  FROM  brokers_comments AS broker
  LEFT JOIN review_author AS author
  ON broker.user_email = author.email
  </code></pre>