help with a difficult query

[Claudio Magno]

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

[Anthony England]

"Claudio Magno" <ma************@libero.it> wrote in message
news:43**********************@reader2.news.tin.it. ..

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

Your structure hints that each shop only sells one product - I guess this is
OK. However, you should not use 'name' and 'date' for field names - they
are reserved words and this can cause you trouble. Change them to
shop_name, price_date, or whatever.
The other point to check is the join. Do you need:
Only shops with at least one price record
All shops regardless of whether they have any price records
A list of all the latest prices regardless of if there is a matching shop
(should not be the case if the database is set up correctly)
The following should give you option 1:

SELECT s.id, s.name, s.address, p.price, p.date
FROM SHOPS AS s INNER JOIN PRICES AS p
ON s.id=p.idshop
WHERE p.id IN
(SELECT TOP 1 id FROM PRICES
WHERE prices.idshop=s.id
ORDER BY PRICES.date DESC, PRICES.id)