Hi guys!
I'm trying to make a so simple SQL Query and I'm embarrassed I Can't get it working. I've been looking around the web and found similar answers, but not exactly what I'm looking for.
We've got 3 different tables:
- CUSTOMERS table:
Fields: CustomerID (PK), CustomerName,
- PRODUCTS table:
Fields: ProductID (PK), ProductName
- PRIZES table:
Fields: CustomerID (FK), ProductID (FK), Date, Prize
Just to say that in Prizes table, a customer can have more than one prize for the same product at different dates. So for a given customer and a given product, only the most recent prize is the correct one.
That said, I want to make a query that shows: for each customer its name and all the related products and its code->for each product only the most recent prize and the date that prize was stated
Code:
SELECT C.CustomerName, P.ProductID, P.ProductName, MAX(Pr.date)
FROM Customers C, Products P, Prizes Pr
WHERE C.CustomerID=Pr.CustomerID AND P.ProductID=Pr.ProductID
GROUP BY C.CustomerName, P.ProductID, P.ProductName;
But with such a query I can't show the prize.
I've also tried with:
Code:
SELECT C.CustomerName, P.ProductID, P.ProductName, Pr.date, Pr.Prize
FROM Customers C, Products P, Prizes Pr
WHERE C.CustomerID=Pr.CustomerID AND P.ProductID=Pr.ProductID AND Pr.date =
( SELECT MAX(PRI.date)
FROM pRIZES PRI
WHERE PRI.CustomerID=Pr.CustomerID AND PRI.ProductID=PR.ProductID
);
But it shows me an error as well.
Could you give a hand?
Kind Regards