Joining top records in T-SQL

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 sta开发者_如何学JAVAtment 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.

---

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.

---

SELECT MD.*, Contact.FirstName  
FROM MerchantData MD 
JOIN Merchant M ON M.MerchID = MD.MerchID    
CROSS APPLY (SELECT TOP 1 * FROM Location WHERE BusID = MD.BusID AND DELETED = 0) L
JOIN Contact ON Contact.ContactID = L.PrincipalID 

---

This is a case of the "top n per group" problem. This question will guide you:

SQL Server query select 1 from each sub-group

You'll want to be doing something like this:

SELECT MD.* ,
       Contact.FirstName 
FROM MerchantData MD
JOIN Merchant     M ON M.MerchID = MD.MerchID   
JOIN ( select * ,
              seq = rank() over( partition by BusID order by BusID , ... )
       from Location
       where L.Deleted = 0
     ) L on L.BusID = MD.BusID
        and seq     = 1
JOIN Contact ON Contact.ContactID = L.PrincipalID

The virtual table expression should return at most 1 Location per BusID (0 if the BusID has no non-deleted Locations).

---

To try and isolate out the error I would try. See if it can match Location.BusID = MD.BusID.

    SELECT MD.*, Contact.FirstName 
    FROM MerchantData MD
    JOIN Merchant M ON M.MerchID = MD.MerchID   
    JOIN Location On Location.BusID = MD.BusID 

You do not use the * so use

    SELECT TOP 1 Location.BusID FROM Location WHERE Location.BusID = MD.BusID 

Once you get the syntax working.

You do know that once you get this working it will only match if the "first" row and then check if it is deleted. The problem is that without an order by the "first" row is arbitrary. Even with a clustered index on a table there is no guaranteed sort without an order by clause. To get a repeatable answer you need a sort. But if you are sorting and only want the top row then a MAX or MIN and a group be more straight forward.

If you want just business that have one or more deleted locations then the following should work but you need to break out the columns for the group by. If two deleted locations have differenct contact name then it will report each. So, this may not be what you are looking for.

    SELECT MD.col1, MD.col2, Contact.FirstName 
    FROM MerchantData MD
    JOIN Merchant M ON M.MerchID = MD.MerchID   
    JOIN Location L
       ON  L.BusID = MD.BusID
       AND L.Deleted = 0
    JOIN Contact ON Contact.ContactID = L.PrincipalID 
    GROUP BY MD.col1, MD.col2, Contact.FirstName