title: MySQL Query limiting results by sub table
body: <p>I'm really struggling with this query and I hope somebody can help. </p>
  
  <p>I am querying across multiple tables to get the dataset that I require. The following query is an anonymised version:</p>
  
  <pre><code>SELECT  main_table.id, 
          sub_table_1.field_1, 
          main_table.field_1, 
          main_table.field_2, 
          main_table.field_3, 
          main_table.field_4, 
          main_table.field_5, 
          main_table.field_6, 
          main_table.field_7,
          sub_table_2.field_1,
          sub_table_2.field_2,
          sub_table_2.field_3,
          sub_table_3.field_1,
          sub_table_4.field_1,
          sub_table_4.field_2
  FROM main_table
  INNER JOIN sub_table_4 ON sub_table_4.id = main_table.id
  INNER JOIN sub_table_2 ON sub_table_2.id = main_table.id
  INNER JOIN sub_table_3 ON sub_table_3.id = main_table.id
  INNER JOIN sub_table_1 ON sub_table_1.id = main_table.id
  WHERE sub_table_4.field_1 = '' AND sub_table_4.field_2 = '0' AND sub_table_2.field_1 != ''
  </code></pre>
  
  <p>The query works, the problem I have is sub_table_1 has a revision number (int 11). Currently I get duplicate records with different revision numbers and different versions of sub_table_1.field_1 which is to be expected, but I want to limit the result set to only include results limited by the latest revision number, giving me only the latest sub_table_1_field_1 and I really can not figure it out!</p>
  
  <p>Can anybody lend me a hand? </p>
  
  <p>Many Thanks.</p>