On 3?20?, ??5?19?, "phil2phil" <philtwop...@yahoo.com> wrote:
> Hi,
> I have two table, Customers and CustomerOrders. Customers has columns
> like CustID, email, firstName, lastName, etc...where CustID is the
> primary key for each Customer. CustomerOrders has the Order info for
> each customer, so if a customer bought 5 times it would have 5
> records, CustomerOrders has a primary key called CUORID and a foriegn
> key of CustID to try back to the user.
> I've got a request to get a list of all Customers and their last order
> amount and date of purchase, both amount and purchase date are ni
> CustomerOrders. I can pull the customer data fine from Customers, but
> doing a join to CustomerOrders is bring me all their orders, but for
> this request i need just the
> last one from CustomerOrders. Is there a way to do this? Or do i have
> to use TempTables, building a query to pull all CustomerOrders entries
> with just last orders from each customer and then join that tempTables
> entries to Customers to get their name, etc...
> Or is there a better and easier way?
> Thanks.
sql2000 :
SELECT C.CustId, C.FirstName, C.LastName,
O.PurchaseDate, O.PurchaseAmount
FROM Customers AS C
JOIN
(SELECT CustId, MAX(PurchaseDate), PurchaseAmount
FROM CustomerOrders GROUP BY CustId,PurchaseAmount ) AS O
ON C. CustId = O.CustId;