CODEWITHSUNDEEP
sql-servert-sql
user1646678
user1646678

Reputation:

Return only 1 address for each customer

I need to query the addresses of top 500 customers (best buyers). Many companies have multiple addresses.

The tables with data:

My query looks this way:

select Customername, CustomerStreet --etc 
from CustomerInfo 
join CustomerAddress on CustomerID = Add_CustID
join  TransactionInfo on Trn_CustID = CustomerID
JOIN TransactionElements ON Trn_CustID = TrE_CustID


GROUP BY CustomerName, CustomerStreet --etc
ORDER BY SUM (TrE_TranValue) DESC

It returns multiple addresses of a single company, I need just one.

Upvotes: 0

Views: 673

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Something like this will work from sqlserver 2005+. I also suggest adding some aliasses to your tables and refer to those.

select CI.Customername, CA.CustomerStreet --etc 
from CustomerInfo CI
cross apply 
(select top 1 Customername, CustomerStreet --etc
from CustomerAddress where CustomerID = CI.Add_CustID) CA
join  TransactionInfo TI on TI.Trn_CustID = CI.CustomerID
JOIN TransactionElements ON CI.CustomerID = TE.TrE_CustID
GROUP BY CustomerName, CustomerStreet --etc
ORDER BY SUM (TrE_TranValue) DESC

Upvotes: 1

marc_s
marc_s

Reputation: 754438

One approach would be to use a CTE (Common Table Expression) if you're on SQL Server 2005 and newer (you aren't specific enough in that regard).

With this CTE, you can partition your data by some criteria - i.e. your CustomerId - and have SQL Server number all your rows starting at 1 for each of those "partitions", ordered by some criteria.

So try something like this:

;WITH CustomerAndAddress AS
(
   SELECT 
       c.Customername, ca.CustomerStreet ,
       ROW_NUMBER() OVER(PARTITION BY c.CustomerId ORDER BY ca.AddressID DESC) AS 'RowNum'
   FROM 
       dbo.CustomerInfo c
   INNER JOIN 
       dbo.CustomerAddress ca ON c.CustomerID = ca.Add_CustID       
   WHERE
      ......
)
SELECT 
   Customername, CustomerStreet 
FROM 
   CustomerAndAddress
WHERE
   RowNum = 1

Here, I am selecting only the "first" entry for each "partition" (i.e. for each CustomerId) - ordered by some criteria (I just arbitrarily picked AddressID from the address - adapt as needed) you need to define in your CTE.

Does that approach what you're looking for??

Upvotes: 1

Yohanes Gultom
Yohanes Gultom

Reputation: 3842

If CustomerInfo has many CustomerAddress, this query will make CustomerInfo returned for each CustomerAddress (a cartesian product) :

join CustomerAddress on CustomerID = Add_CustID

So if you need to get only one address you have to add conditions needed to choose single CustomerAddress :

join CustomerAddress on CustomerID = Add_CustID where <conditions>

Upvotes: 0

Related Questions