title: MySQL: where max date in joined table less than
body: <p>I have 3 tables - <code>client</code>, <code>invoice</code> and <code>invoice_item</code>. I need to select all clients, that have last date purchase less than specific date. Date purchase stored in <code>invoice_item</code> table, items assigned to an invoice, and invoice assigned to a client. Here is what I tried</p>
  
  <pre><code>SELECT c.* FROM client c
  INNER JOIN (
              SELECT i.client_id as clid, MAX(item.date_created) as mdt
              FROM invoice i
              INNER JOIN invoice_item item on i.id = item.invoice_id
              GROUP BY i.id
              ) joined
  ON joined.clid = c.id
  WHERE joined.mdt  &lt; date('2017-01-01')
  </code></pre>
  
  <p>And also </p>
  
  <pre><code>SELECT c.* FROM client c
  WHERE c.id IN (
              SELECT DISTINCT i.client_id FROM invoice i
              INNER JOIN invoice_item item on i.id = item.invoice_id
              GROUP BY i.id
              HAVING MAX(item.date_created) &lt; date('2017-01-01')
              )
  </code></pre>
  
  <p>Both queries return wrong result, here is a query I'm using to check correctness:</p>
  
  <pre><code>SELECT MAX(invoice_item.date_created) FROM invoice_item
  INNER JOIN invoice i on invoice_item.invoice_id = i.id 
  WHERE client_id = {some id from the previous query};
  </code></pre>
  
  <p>The query above returns date, greater than <code>2017-01-01</code> for some clients.</p>
  
  <p>What I'm doing wrong? Thanks.</p>