title: MySQL - Retrieve the max value of an associated column within a LEFT JOIN with a different perimeter than the WHERE clause of the main query
body: <p>I'm using <strong>MySql 5.6</strong> and have a select query with a LEFT JOIN but i need to retrieve the max of a associated column <code>email_nb</code>) but with a different &quot;perimeter&quot; of constraints.</p>
  <p>Let's take an example: let me state that it is a mere example with only 5 rows but it should work also when I have thousands... (I'm stating this since there is a <code>LIMIT</code> clause in my query)</p>
  <p><strong>Table 'query_results'</strong></p>
  <pre><code>+-----------------------------+------------+--------------+
  | query_result_id             | query_id   | author       |
  +-----------------------------+------------+--------------+
  | 2                           |         1  | john         |
  | 3                           |         1  | eric         |
  | 7                           |         3  | martha       |
  | 9                           |         4  | john         |
  | 10                          |         1  | john         |
  +-----------------------------+------------+--------------+
  </code></pre>
  <p><strong>Table 'customers_emails'</strong></p>
  <pre><code>+-------------------+-----------------+--------------+-----------+-------------+------------------------
  | customer_email_id | query_result_id | customer_id  | author    |  email_nb   | days_since_sending
  +-------------------+-----------------+--------------+-----------+-------------+------------------------
  | 5                 |         2       | 12           |  john     |   2         |  150
  | 12                |         3       | 7            |  eric     |   4         |  90
  | 27                |         3       | 12           |  eric     |   2         |  86
  | 40                |         9       | 15           |  john     |   9         |  87
  | 42                |         2       | 12           |  john     |   7         |  23
  | 51                |         10      | 12           |  john     |   3         |  89
  +-------------------+-----------------+--------------+-----------+-------------+-----------------------
  </code></pre>
  <p><strong>Notes:</strong></p>
  <ul>
  <li><p>you can have a <code>query_result</code> where the author appears in NO row at all in any of the <code>customers_emails</code>, hence the <code>LEFT JOIN</code> I'm using.</p>
  </li>
  <li><p>You can see <code>author</code> is by design kind of duplicated as it's both on the first table and the second table each time associated with a <code>query_result_id</code>. It's important to note.</p>
  </li>
  <li><p><code>email_nb</code> is an integer between 0 and 10</p>
  </li>
  <li><p>there is a <code>LIMIT</code> clause as I need to retrieve a set number of records</p>
  </li>
  </ul>
  <p>Today my query aims at retrieving <code>query_results</code> with a certain number of conditions on The specificity is that I make sure to retrieve query_results with an <code>author</code> who does not appear in any <code>customer_email_id</code> where the <code>days_since_sending</code> would be less than 60 days: it means i check these <code>days_since_sending</code> not only within the records for this query, but <strong>across all <code>customers_emails</code></strong> thanks to the subquery <code>NOT IN</code> (see below).</p>
  <p>This is <strong>my current query</strong> for <code>customer_id = 12</code> and <code>query_id = 1</code></p>
  <pre><code>  SELECT             
      qr.query_result_id,     
      qr.author,
    FROM
      query_results qr
    LEFT JOIN
      customers_emails ce
    ON
      qr.author = ce.author           
    WHERE
      qr.query_id = 1 AND
      qr.author IS NOT NULL            
        AND qr.author NOT IN (
          SELECT recipient
          FROM customers_emails
          WHERE               
            (
              customer_id = 12 AND
              ( days_since_sending &gt;= 60) ) 
            )           
        )     
    # we don't take by coincidence/bad luck 2 query results with the same author
    GROUP BY
      qr.author
    ORDER BY 
      qr.query_result_id ASC
    LIMIT 
      20
  </code></pre>
  <p>This is the expected output:</p>
  <pre><code>+-----------------------------+------------+--------------+
  | query_result_id             | author     | email_nb     |  
  +-----------------------------+------------+--------------+
  |        10                   |   john     |    7         |
  |        3                    |   eric     |    2         |   
  +-----------------------------+------------+--------------+
  </code></pre>
  <p><strong>My challenge/difficulty today:</strong></p>
  <ul>
  <li><p>Notice on the 2nd line Eric is tied to <code>email_nb</code> <code>2</code> and not the max of all Eric's emails which could have been <code>4</code> if we had taken the max of <code>email_nb</code> across ALL messages to <code>author=eric</code>. but we stay within the limit of <code>customer_id = 12</code> so there's only one left with <code>email_nb = 2</code></p>
  </li>
  <li><p>Also notice that on the first line, the <code>email_nb</code> associated with <code>query_result = 10</code> is <code>7</code>, and not <code>3</code>, which could have been the case as <code>3</code> is what appears in table <code>customers_emails</code> on the last line.</p>
  </li>
  <li><p>Indeed for emails to 'john' i had the choice between <code>email_nb</code> <code>2</code>, <code>7</code> and <code>3</code> but I take highest so it's <code>7</code> (even if this email is from more than 60 days ago !! This is very important and part of what I don't know how to do: the perimeters are different: today I retrieve all the query_results where the <code>author</code> has NOT been sent a email for the past 60 days (see the <code>NOT IN</code> subquery) BUT I need to have in the column the max <code>email_nb</code> sent to <code>john</code> by <code>customer_id=12</code> and <code>query_id=1</code> EVEN if it was sent more than 60 days ago so these are different perimeters...Don't really know how to do this...</p>
  </li>
  <li><p>It means in other words I don't want to find the max (email_nb) within the same <code>WHERE</code> clauses such as <code>days_since_sending &gt;= 60</code> or within the same LIMIT and GROUP BY...as my current query: what I neeed is to retrieve the maximum value of <code>email_nb</code> for <code>customer_id=12</code> AND <code>query_id=1</code> and sent to <code>john</code> across <strong>ALL records on the <code>customers_emails</code> table!</strong></p>
  </li>
  <li><p>If there is no associated row on customers_emails at all (it means no email have been ever sent by this customer for this query in the past) then the email_nb should be sth like NULL..</p>
  </li>
  </ul>
  <p>This means I do NOT want this output:</p>
  <pre><code>+-----------------------------+------------+--------------+
  | query_result_id             | author     | email_nb     |  
  +-----------------------------+------------+--------------+
  |        10                   |   john     |    3         |
  |        3                    |   eric     |    2         |   
  +-----------------------------+------------+--------------+
  </code></pre>
  <p>How to achieve this in MySQL 5.6 ?</p>