title: MYSQL Advanced select distinct values with corresponding ID which are not in anothe table
body: <p>EDITED:</p>
  
  <p>I have a huge problem with <code>SELECT</code> <code>DISTINCT</code> values from one of my table.</p>
  
  <p>Table 1 :<code>T1</code></p>
  
  <pre><code>pid      thing       sub-thing     tnumber     
  -------------------------------------------
  1       A1212       A01A00001     123456      
  2       A1212       A01A00002     123457     
  3       A1212       A01A00002     123458      
  4       A1214       A01B00001     123459     
  5       A1214       A01B00002     123460        
  6       A1214       A01B00001     123461         
  7       A1217       C01A00001     123462     
  </code></pre>
  
  <p>Table 2 :<code>T2</code></p>
  
  <pre><code>id        pid      thing       sub-thing         tnumber    h1 
  -------------------------------------------------------------- 
  1          3            A1212       A01A00002     123458   False
  </code></pre>
  
  <p>I need to <code>SELECT</code> all <code>DISTINCT</code> sub-things for every thing from Table <code>T1</code> which are NOT in Table <code>T2</code> and, most important - only sub-thing with the highest tnumber (for ex. with max(id)).</p>
  
  <p>Result Should leave records 2, 4 from <code>T1</code> because there are identical sub-things with higher tnumber and of course leave record 3 from <code>T1</code> because it's in the <code>T2</code>.
  Final result should be like this</p>
  
  <pre><code>id      thing       sub-thing     tnumber     
  -------------------------------------------
  1       A1212       A01A00001     123456                 
  5       A1214       A01B00002     123460        
  6       A1214       A01B00001     123461         
  7       A1217       C01A00001     123462  
  </code></pre>
  
  <p>Thanks to everyone here, the syntax I've tried is:</p>
  
  <pre><code>SELECT DISTINCT t1.pid, t1.thing, t1.subthing, t1.tnumber 
  FROM t1 INNER JOIN
  (SELECT t1.thing, t1.subthing, max(t1.tnumber) as tnumber FROM t1 LEFT OUTER JOIN t2 o ON t1.pid=o.pid WHERE o.pid IS NULL
  AND t1.added&gt;'2015-10-31' 
  GROUP BY t1.subthing, thing) 
  as b using (subthing, thing, tnumber) ;
  </code></pre>
  
  <p>Now I need to add a condition that the records from <code>T1</code> where <code>T1.pid</code> is NOT in <code>T2.pid</code> AND <code>t2.h1=false</code> should NOT be given in final result.</p>