title: SQL query help with GROUPing and nested queries
body: <p>Given are two tables,</p>
  
  <ul>
  <li><p>Table <code>A</code> containing <code>customerid</code>, <code>lastchange</code>, <code>internallink</code></p></li>
  <li><p>Table <code>B</code> containing <code>internallink</code>, <code>turnover</code> </p></li>
  </ul>
  
  <p><em>(I'm simplyfying here and changing it to a generic example, the actual structure is more complex. SQL dialect for now is mySQL.)</em></p>
  
  <p>The only unique thing (per table) is the internallink.
  There are several records in A with the same customerID, different dates in lastchange, and different internallink values.
  There are other items linked with this; I cannot change the tables.</p>
  
  <p><strong>I need the recordIDs from A which are the most recent for a customer (highest lastchange value of all with the same customerID) <em>and</em> to which the entries in B which match a certain value condition are linked.</strong></p>
  
  <p>I think the </p>
  
  <pre><code>SELECT `internallink` FROM `B` WHERE (`turnover` &gt; 10000) 
  </code></pre>
  
  <p>part is not the issue.</p>
  
  <p>I got this far:</p>
  
  <pre><code>SELECT `customerID`, MAX(`lastchange`)
    FROM `A` 
   WHERE `lastchange` IN (SELECT `internallink` FROM `B` 
                           WHERE `turnover` &gt; 10000)
   GROUP BY `customerID`;
  </code></pre>
  
  <p>Alas, that statement gives wrong results, because above will return me customerIDs for which the most recent value does not fulfill the criteria, but some older did - it selects the oldest that did, and returns this. But in case the most recent entry is below threshold, the customerID should not turn up at all.</p>
  
  <p>Where did I go wrong, and what is the correct approach to this?</p>
  
  <p>Sample Data
  Table A</p>
  
  <pre>
  customerid   lastchange   internallink
           3   2010-02-11   11
           3   2010-09-04   12
           3   2010-10-22   13
           3   2010-11-23   14
           4   2010-05-05   15
           4   2010-12-01   16
           5   2010-11-28   17
           5   2010-11-29   18
  </pre>
  
  <p>Table B</p>
  
  <pre>
  internallink  turnover
            11     47000
            12     11000
            13      8000
            14     15000
            15     17000
            16     23000
            17     50000
            18     10000
  </pre>
  
  <p>The actual threshold in my tests is 12000.
  You can see customerID should not be in the result set, since the most recent entry is below the threshold.</p>
  
  <p>The result set should be (3,2010-11-23)(4,2010-12-01) - but currently it also contains (5,2010-11-28), which is wrong.</p>
  
  <hr>
  
  <p>Getting a bit closer (with your help, thanks!), these two statements both work:</p>
  
  <p>SELECT customerID, MAX(lastchange), internallink FROM A GROUP BY customerID;
     SELECT internallink FROM B WHERE (turnover > 12000);</p>
  
  <p>Now all I need is the intersection of both... with the correct logic! </p>