Re: Doubt about join queries - Mailing list pgsql-general

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