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]

Re: help with a difficult query


"Claudio Magno" <magnTOGGHIatti @libero.it> wrote in message
news:43c4f36b$0 $1069$4fafbaef@ reader2.news.ti n.it...[color=blue]
> 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[/color]


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)