You've understood very well my problem, but also this query works as worse than everything I did before, it throws as many rows as rows are contained my tables clients and stores. I only want to find for every client what store is closer to him, I expect one client to one store and their distance Thanks a lot Bill Moran wrote: > > In response to jc_mich <juan.michaca@xxxxxxxxxx>: >> >> 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/ > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23146909.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
