title: MySQL Get latest record by date from mutiple tables with values from lookup tables
body: <p>I want to get the latest MainNumber, Serial, BarType and Notes for a given <code>MainNumber</code>, if they exist. Note that BarType is stored in a lookup table and referenced with BarID.</p>
  
  <p>Unreason came up with this:</p>
  
  <pre><code>SELECT @MainNumber, COALESCE(n.Notes, 'None')
  FROM numbers 
       LEFT JOIN notes n ON numbers.MainNumber = n.MainNumber
       LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date &lt; n2.Date
  WHERE n2.Date IS NULL AND numbers.MainNumber = @MainNumber
  </code></pre>
  
  <p>This is fine whether Notes is <code>NULL</code> or not, but now I need the Serial and the BarType. A MainNumber may have been assigned to multiple Serials during its lifetime, but I only want the latest Serial. (I'll need to do this with about 15 other fields in other tables, so a performant answer would be appreciated where possible)</p>
  
  <p><strong>Tables</strong></p>
  
  <p>Numbers Table:</p>
  
  <pre><code>CREATE TABLE `numbers` (
    `ID` int(10) unsigned NOT NULL auto_increment,
    `MainNumber` varchar(11) NOT NULL,
    `Serial` varchar(20) NOT NULL,
    `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY  (`ID`),
    UNIQUE KEY `Serial` (`Serial`)
  ) ENGINE=MyISAM AUTO_INCREMENT=460 DEFAULT CHARSET=latin1
  </code></pre>
  
  <p>Notes table:</p>
  
  <pre><code>CREATE TABLE `notes` (
    `ID` int(10) unsigned NOT NULL auto_increment,
    `MainNumber` varchar(11) NOT NULL,
    `Notes` longtext NOT NULL,
    `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY  (`ID`),
    KEY `MainNumber` (`MainNumber`)
  ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
  </code></pre>
  
  <p>ref_bars table:</p>
  
  <pre><code>CREATE TABLE `ref_bars` (
    `BarID` varchar(6) NOT NULL,
    `BarType` varchar(30) NOT NULL,
    PRIMARY KEY  USING BTREE (`BarID`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  </code></pre>
  
  <p>bars table:</p>
  
  <pre><code>CREATE TABLE `bars` (
    `ID` int(10) unsigned NOT NULL auto_increment,
    `MainNumber` varchar(11) NOT NULL,
    `BarID` varchar(6) NOT NULL,
    `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY  (`ID`),
    KEY `MainNumber` (`MainNumber`)
  ) ENGINE=MyISAM AUTO_INCREMENT=212 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
  </code></pre>
  
  <p><strong>Sample Data</strong></p>
  
  <p><code>SELECT * FROM notes</code>:</p>
  
  <pre><code>'ID','MainNumber','Notes','Date'
  '1','1','repaired','2009-03-23 12:00:00'
  '2','1','replaced','2009-08-15 19:20:05'
  </code></pre>
  
  <p>Note: two rows for MainNumber = 1, but no row for a MainNumber of 2. The IDs are just technical and are never used.</p>
  
  <p><code>SELECT * FROM numbers</code>:</p>
  
  <pre><code>'ID','MainNumber','Serial','Date'
  '1','1','4642785154854','2008-08-15 12:30:00'
  '2','1','4642315642316','2009-08-15 12:50:00'
  '3','2','5412558456223','2010-08-15 11:30:00'
  </code></pre>
  
  <p><code>SELECT * FROM bars</code>:</p>
  
  <pre><code>'ID','MainNumber','BarID','Date'
  '1','1',1,'2008-08-15 12:30:00'
  '2','1',2,'2009-08-15 12:50:00'
  '3','2',2,'2010-08-15 11:30:00'
  </code></pre>
  
  <p><code>SELECT * FROM ref_bars</code>:</p>
  
  <pre><code>'BarID','BarType'
  '1','Banned'
  '2','Payment required'
  </code></pre>
  
  <p><strong>Expected Output</strong></p>
  
  <p>MainNumber = 1</p>
  
  <pre><code>MainNumber,Notes,Banned,Unpaid
  '1','replaced','Yes','Yes'
  </code></pre>
  
  <p>MainNumber = 2</p>
  
  <pre><code>MainNumber,Notes,Banned,Unpaid
  '2','None','No','Yes'
  </code></pre>
  
  <p>Edit: Fixed it and tested it, whilst making things clearer (hopefully). I was rushed off to do other things earlier today, sorry for wasting people's time with a badly-written, incomplete question.</p>
  
  <p>Updated to clarify the more complex requirements</p>