Re: Doubt about join clause

In response to jc_mich <juan(dot)michaca(at)paasel(dot)com>:
>
> Hello
>
> I have a table with clients and other with stores, I want to calculate
> minimum distances between stores and clients, the client name and its closer
> store.
>
> At this moment I can only get clients ids and minimum distances grouping by
> client id, but when I try to join their respective store id, postgres
> requires me to add store id in group clause and it throws as many rows as
> the product of number clients and stores. This result is wrong, I only
> expect the minimum distance for every client.
>
> My code looks like this:
>
> SELECT distances.client_id, min(distances.distance) FROM(
> SELECT stores.id AS store_id, clients.id AS client_id,
> sqrt(power(store.x)+power(store.y)) AS distance
> FROM stores, clients
> WHERE 1=1
> ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;
>
> Also I've tried this:
> SELECT clients.id, MIN(distances.distance)
> FROM stores, clients LEFT JOIN(SELECT clients.id AS client_id, stores.id,
> sqrt(power(stores.x)+power(stores.y)) AS distance
> FROM stores, clients
> WHERE 1=1) distances
> ON distances.client_id = clients.id GROUP BY clients.id
>
> Thanks in advance!

Something like this should work, (assuming I understand your tables):

SELECT clients.id, stores.id,
min(sqrt(power(clients.x-stores.x)+power(clients.y-stores.y))
FROM clients, stores
GROUP BY clients.id, stores.id;

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/