title: LEFT OUTER JOIN (SELECT * FROM TABLE) is this possible?
body: <p>I have two tables <code>Person</code> and <code>Salary</code>.</p>
  
  <p><code>Person</code>:</p>
  
  <pre><code>PersonId   |  Name   |   Surname
  --------------------------------
         1      John          Deer
         2      Mark          Bear
  </code></pre>
  
  <p><code>Salary</code>:</p>
  
  <pre><code>SId | PersonId | Date                       | Salary
  ----------------------------------------------------
  1            2   2013-01-01 00:00:00.000      100
  2            2   2012-01-01 00:00:00.000      90
  3            2   2011-01-01 00:00:00.000      80
  </code></pre>
  
  <p>What I am trying to do is, if a person has a salary record then it should display the most current salary info in the results, if no salary record then it should display the salary info as null, which is like... </p>
  
  <pre><code>Result
  ------------------------------------------------------------------------
  PersonId   |   Name   | Surname  |  Date                      |  Salary
         1       John        Deer     NULL                            NULL
         2       Mark        Bear     2013-01-01 00:00:00.000         100
  </code></pre>
  
  <p>I know it has to be something like this but with lack of knowledge I just couldn't achieve..</p>
  
  <pre><code>SELECT 
      P.PersonId, P.Name, P.Surname, SL.Date, SL.Salary
  FROM 
      PERSON P
  LEFT OUTER JOIN 
      (SELECT TOP 1 S.PersonId, S.Date, S.Salary 
       FROM Salary 
       WHERE S.PersonId = P.PersonId ORDER BY Date DESC) SL
  </code></pre>