title: SQL use nested select in middle of inner join
body: <p>Is it possible to use a select in the middle of joining...</p>
  
  <p>I am trying to do the following:</p>
  
  <pre><code>FROM 
  tblorders o 
  INNER JOIN tblunits u on o.id = u.orderid
  INNER JOIN ((SELECT
                     ,Min(n.date) as [MinDate]
                from tblNotes n
               Where n.test = 'test') te
  INNER JOIN tblnotes n on te.id = n.id
                       and te.[MinDate] = n.AuditinsertTimestamp)
  INNER Join tblClient c ON o.ClientId = c.Id
  </code></pre>
  
  <p>Basically in the select in the middle of the query it is selecting only the notes with min date. The problem is I need to do this here because I need from tblOrders to be the first table.......</p>
  
  <p>Suggestions?</p>