Tricky Query, what do I do?

[barryna]

Ok, I have two tables, a customer table and a product sales table. Here is the relavent info:

Customers
-CustomerID
-CustomerName
-IsActive

ProductSales
-CustomerID
-ProductName
-DateSold

Here is what I need. It needs to be done in one query. I need a list of customers who are active and have the last product sold (if any) attached to their record. How do I do this?

Example:

Customers
1000,Todd,Yes
1001,Nick,No
1002,Tim,Yes

ProductsSales
1000,Product1,1/1/2005
1000,Product2,4/1/2005

The query would then output this info:
1000,Todd,Yes,Product2,4/1/2005
1002,Tim,Yes,NULL,NULL

This is not a school assignment or anything, I need to do this at work. I am very proficient with SQL but this one is stumping me!

Any help is greatly appreciated.

Nick

 

[PHV]

Something like this ?
SELECT C.CustomerID, C.CustomerName, C.IsActive, P.ProductName, P.DateSold
FROM Customers C LEFT JOIN (
(SELECT CustomerID, Max(DateSold) AS LastSold FROM ProductSales GROUP BY CustomerID
) S LEFT JOIN ProductSales P ON S.CustomerID = P.CustomerID AND S.LastSold = P.DateSold
) ON C.CustomerID = S.CustomerID
WHERE C.IsActive = Yes;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[barryna]

Works great except for one thing. If more than one product was sold to a customer on a given day, then I am getting the customer listed twice in the query. I need the customer listed once, with the most recent product entered (there is an index on the ProductSales table called Index), so the max indexed product sold if there is more than one product sold on a day. So it would be the max date sold and then max index sold if more than one on a date. Hope that makes sense.