Geek Answers Handbook

Merging 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 are several times several places for busid, and I want to join only the first ones found. I get the error "Location.BusID = MD.BusID" because MD.BusID cannot be bound. Is it possible to use the MD table in a nested select element in this join, or is there any other way to accomplish this?

I am collecting data using nested queries in a column list to get contact details.

+4
source share
4 answers

It would be simpler if I had a subquery of the full set of results:

 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 entry per BusID in the JOIN , but not correlated.

+8
source 
 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
+4
source

This is a case of a top n per group problem. This question will help you:

SQL Server query selects 1 from each subgroup

You want to do 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 must have no more than 1 place on the bus identifier (0 if the BusID has no remote locations).

+2
source

To try to isolate 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 are not using *, so use

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

As soon as you get the syntax.

You know that as soon as you get this work, it will only correspond to the first line, and then check whether it is deleted. The problem is that without ordering on the "first" line is arbitrary. Even with a clustered table index, there is no guaranteed sorting without an order by clause. To get a repeatable answer, you need a view. But if you sort and want only the top row, then MAX or MIN and the group will be more direct.

If you want to deal with only one or more remote locations, then the following should work, but you need to split the columns for the group. If two remote locations have a different contact name, it will report all. 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
0
source

More articles:


All Articles