title: Joining top records in T-SQL
body: <pre><code>SELECT MD.*, Contact.FirstName 
  FROM MerchantData MD
  JOIN Merchant M ON M.MerchID = MD.MerchID   
  JOIN (SELECT TOP 1 * FROM Location WHERE Location.BusID = MD.BusID) L ON L.BusID=MD.BusID
  AND L.Deleted = 0
  JOIN Contact ON Contact.ContactID = L.PrincipalID
  </code></pre>
  
  <p>I am using SQLSERVER 2008 and trying to write this SQL statement. There is some times multiple locations for a busid and I want to join in only the first found. I am getting an error on the part "Location.BusID = MD.BusID" as MD.BusID cannot be bound. Is it possible to use the MD table in the nested select statment in this join or is there another way of accomplishing this? </p>
  
  <p>I am contiplating putting the data using nested querys in the column list to grab the contact data driectly there.</p>