Joining top records in T-SQL

Question

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

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?

I am contiplating putting the data using nested querys in the column list to grab the contact data driectly there.

Answer 1

It would be simpler I think to have a subquery of the full result set:

SELECT MD.*, Contact.FirstName 
FROM MerchantData MD
JOIN Merchant M ON M.MerchID = MD.MerchID   
JOIN (SELECT BusID, MAX(PrincipalID)
      FROM Location
      WHERE Deleted = 0
      GROUP BY BusID) L ON L.BusID=MD.BusID
JOIN Contact ON Contact.ContactID = L.PrincipalID

You still get one record per BusID in the JOIN but it's not correlated.