Hi all,
in short here it is my problem:
I have to monitor the prices of a product sold by different shops and I need
a query to retrieve a list of shops with the associated CURRENT price of the
product.
The structure is modelled by 2 tables in a 1 to n relation:
SHOPS
id counter
name text
address text
&
PRICES
id counter
price value
date text
idShop numeric
How can I obtain a result of this kind:
idShop name address price date
with all recorded shops showing the most recent date and price?
I was only able to retrieve the current date with the query:
SELECT B.IDSHOP,MAX(B.DATE) AS LU, A.NAME, A.ADDRESS FROM PRICES B LEFT
OUTER JOIN SHOPS A ON B.IDSHOP=A.ID GROUP BY IDSHOP, A.NAME, A.ADDRESS
but if I try to add to the select the price, the group by clause gives
(correctly) a result not suitable for my aims.
I also tried with subqueries but I had the same results as above.
Any hint?
TIA
Claudio