title: T-SQL Select join with condition
body: <p>Lets say that i have a 3 tables:</p>
  
  <ol>
  <li><p><strong>Cars</strong></p>
  
  <ul>
  <li>Id</li>
  </ul></li>
  <li><p><strong>CarColorHistory</strong></p>
  
  <ul>
  <li>Id</li>
  <li>CarID</li>
  <li>ColorID</li>
  <li>ModificationDate</li>
  </ul></li>
  <li><p><strong>Color</strong>:</p>
  
  <ul>
  <li>Id</li>
  <li>ColorName</li>
  </ul></li>
  </ol>
  
  <p>I want to select all cars and their colors but the important thing is, that color for the car is the last modified color from <code>CarColorHistory</code> table.</p>
  
  <p>I need to use join to do this.</p>
  
  <p>Example:</p>
  
  <p>Cars:</p>
  
  <pre><code>1
  2
  </code></pre>
  
  <p>CarColorhistory:</p>
  
  <pre><code>1 1 1 26/03/2012  -&gt; (actual color, can be take by date or id)
  2 1 2 25/03/2012
  3 2 2 25/03/2012
  </code></pre>
  
  <p>Color:</p>
  
  <pre><code>1 Blue
  2 Red
  </code></pre>
  
  <p>I need to get result: (car id, colorName)</p>
  
  <pre><code>1 Blue
  2 Red
  </code></pre>
  
  <p>I tried make it by joining Cars table and CarColorHistory table but I get cars for all colors. I need only actual color (last added).</p>
  
  <p>Please help</p>