title: MySQL - nested select statement for JOIN?
body: <p>I have 3 tables that are currently being joined using inner joins. The tables are:</p>
  
  <p>invoice, contract and meter.</p>
  
  <p>Some simplified sample data:</p>
  
  <pre><code>//invoice
  
  id | contract_id
  
  1  | 123
  
  //contract
  
  id  | meter_id | supplier     | end_date
  
  123 | 100      | British Gas  | 2013-12-20
  
  456 | 100      | nPower       | 2014-03-03
  
  //meter
  
  id  | meter-id
  1   | 100
  </code></pre>
  
  <p>My aim is to join the tables but retrieve only the latest (MAX) end_date and get the supplier. Normally this wouldn't be a problem, but I only have contract 123 to join on, not contract 456. As shown, they both share the same meter_id.</p>
  
  <pre><code>//Current query
  SELECT
  contract.supplier AS supplierName
  FROM invoice
  INNER JOIN contract ON contract.id=invoice.contract_id
  INNER JOIN meter ON meter.id=contract.meter_id
  </code></pre>
  
  <p>How do I do this? Is it via a nested select or something?? Thanks</p>