title: SQL Join to get most recent record
body: <p>I have three tables:</p>
  
  <ul>
  <li><code>Measurements (MeasureID, Time, Distance, Value)</code></li>
  <li><code>Events(EventID, Time Value)</code></li>
  <li><code>EventValues (EventDataID, EventID, Type, Value)</code></li>
  </ul>
  
  <p>I need to get for every measurement, the most recent event (in the past) and its associated eventvalues data.</p>
  
  <p>My current query is quite ugly:</p>
  
  <pre><code>SELECT 
      M.*,
      (SELECT TOP 1 EV.value FROM [Event] E JOIN EventValues EV ON E.EventID = EV.EventID 
       WHERE M.Time &gt;= E.Time ORDER BY M.Time-E.Time) AS Data,
  FROM [Measure] M 
  ORDER BY M.Distance
  </code></pre>
  
  <p>and it only allows me to select one column from the <code>EventValues</code> table (I need more)</p>
  
  <p>Is there any way this can be done using a join?</p>
  
  <p><strong>EDIT</strong>: I also need to select ALL entries from the measurement table even if they are before the first event (i.e. just select null data for the join)</p>