From: | Bill Moran <wmoran(at)potentialtech(dot)com> |
---|---|
To: | jc_mich <juan(dot)michaca(at)paasel(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Doubt about join clause |
Date: | 2009-04-20 20:28:32 |
Message-ID: | 20090420162832.7f9a5a98.wmoran@potentialtech.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew Pugsley | 2009-04-20 20:34:25 | update one table with another |
Previous Message | Matthew Pugsley | 2009-04-20 20:23:34 | update one table with another |