title: Select max value from joined table
body: <p>i need help with a mysql query. My tables:</p>
  
  <p>objects</p>
  
  <pre><code>+---------+--------+
  | id      | name   |
  +---------+--------+
  |    1    | house 1|
  |    2    | house 2|
  |    3    | house 3|
  +---------+--------+
  </code></pre>
  
  <p>objects_expire</p>
  
  <pre><code>+----------+-----------+
  | object_id| expire    |
  +----------+-----------+
  |    1     | 2014-09-11|
  |    1     | 2015-09-11|
  |    2     | 2014-09-11|
  |    2     | 2015-09-11|
  |    2     | 2016-09-11|
  |    3     | 2013-09-11|
  |    3     | 2014-09-11|
  |    3     | 2015-09-15|
  +----------+-----------+
  </code></pre>
  
  <p>Now i need objects where max 'expire' is bigger then 2015-09-04 and smaller then 2015-09-18 (+/- 7 days)</p>
  
  <p>Like this result:</p>
  
  <pre><code>+----------+-----------+-----------+
  | object_id| expire    | name      |
  +----------+-----------+-----------+
  |    1     | 2015-09-11| house 1   |
  |    3     | 2015-09-15| house 3   |
  +----------+-----------+-----------+
  </code></pre>
  
  <p>This is what i have now:</p>
  
  <pre><code>SELECT o.id, MAX(uio.expire) AS object_expires
  FROM objects AS o
  LEFT JOIN objects_expire AS oe ON oe.object_id = o.id
  WHERE expire &lt; '2015-09-18'
  AND expires &gt; '2015-09-04'
  GROUP BY o.id
  </code></pre>
  
  <p>But thats not correct.</p>
  
  <p>Thanks for any help!!!</p>