title: MySQL - Left Join on MAX timestamp
body: <p>I know this is a common question, because I've asked it before, and have looked at several more, but still, despite basing my query on this, I cannot get it to work. see: <a href="https://stackoverflow.com/questions/1373559/mysql-join-based-on-maxtimestamp">Mysql join based on max(timestamp)</a> and <a href="https://stackoverflow.com/questions/6267878/mysql-left-join-for-right-table-max-value">mysql LEFT join for right table max value</a></p>
  
  <p>Basically I have a tables of different items ('chairs', 'tables') and each type has record with their own id for specific chairs or tables. Then I have a location table which keeps all the locations that have ever been, including the current one. The current location is noted by the MAX timestamp. </p>
  
  <p>My query is meant to get all the items, and join to the current location. I have followed what others have said works, but it isn't working for me. Any help is appreciated.</p>
  
  <p>EDIT: I didn't say how it fails: It gets records and makes the join, and it returns the max timestamp, but not the record info that corresponds with the MAX(timestamp) record. So it gives the right timestamp, but the floor and etc are NOT from the record with the MAX timestamp.</p>
  
  <p>Thanks!</p>
  
  <p>The query:</p>
  
  <pre><code>$q = "SELECT
          'chairs' AS work_type,
          chairs.id AS work_id,
          chairs.title,
          chairs.dimensions,
          CurrentLocations.floor,
          CurrentLocations.bin,
          CurrentLocations.bay,
          CurrentLocations.other
         FROM chairs
               LEFT JOIN ( 
                          SELECT 
                                locations.id AS loc_id,
                                locations.work_type AS clwt,
                                locations.work_id AS clwi,
                                locations.floor,
                                locations.bin,
                                locations.bay,
                                locations.other,
                                MAX(locations.timestamp) AS latestLocation
                              FROM
                                 locations
                              GROUP BY
                                 locations.work_type, locations.work_id
                              ) CurrentLocations
                  ON CurrentLocations.clwi = chairs.id AND CurrentLocations.clwt = 'chairs'
          WHERE cur_loc LIKE '%19th Street%'
          ORDER BY 
              FIELD(floor, 'Basement', '3rd Floor', '4th Floor', '5th Floor'), 
              bin, 
              bay,
              other";
  </code></pre>