Select columns from several tables with count

Question

I have 3 tables in SQL Server:

• Sales (customerId)
• Customer (customerId, personId)
• Person (personId, firstName, lastName)

and I need to return the top 10 customers.

I used this query:

SELECT TOP 10 
    CustomerID, COUNT(CustomerID)
FROM
    Sales
GROUP BY
    (CustomerID)
ORDER BY 
    COUNT(CustomerID) DESC

The query currently returns only the customerId and count, but I also need to return the firstName and lastName of these customers from the Person table.

I know I need to reach the firstName and lastName by correlating between Sales.customerId and Customer.customerId, and from Customer.personId to get the Person.personId.

My question is whether I need to use an inner join or union, and how to use either of them to get the firstName and lastName of these customers

Answer 1

Union is mostly used for disjoint sets. To achieve your target, u can go with inner-join.

If you want to use joins, then here is the query which works similarly to your requirement.

SELECT TOP 10 S.CustomerID, P.FirstName,P.LastName, count(*) 
FROM Sales S 
   INNER JOIN Customer C on S.CustomerId=C.CustomerId
   INNER JOIN Person P on C.PersonId = P.PersonId
GROUP BY (S.CustomerID, P.FirstName,P.LastName) 
ORDER BY count(*) DESC