Re: Doubt about join queries

The problem is not the formula, the problem is the join. I've summarized it
because is a large formula and I've got right results

Here is the complete code code, stores and clients have a longitude and
latitude, and ponderator is a demand variable:

SELECT distances.client_id, min(distances.dist) FROM(
SELECT stores.id AS store_id, clients.id AS client_id,

6378*stores.ponderator*acos(cos((90-clients.lat)*(pi()/180))*cos((90-stores.lat)*(pi()/180))+sin((90-clients.lat)*(pi()/180))*sin((90-stores.lat)*(pi()/180))*cos((clients.lon-stores.lon)*(pi()/180)))
AS dist
FROM stores, clients
WHERE 1=1
ORDER BY stores.id, dist) AS distances GROUP BY distances.client_id;

I only want to assign to each store a client in function of the minimum
distance between them.

Thanks

Best regards

Filip Rembiałkowski-3 wrote:
>
> 2009/4/20 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
>>
>
>
> It would be much easier if you show actual database schema.
>
> It is not clear what is the meaning of stores.x and stores.y variables -
> what do they measure. If they are just coordinates, then where are client
> coordinates stored?
>
>
>
>
> --
> Filip Rembiałkowski
> JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
> http://filip.rembialkowski.net/
>
>

--
View this message in context: http://www.nabble.com/Doubt-about-join-queries-tp23142980p23159394.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.