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

From David Wilson
Subject Re: Doubt about join clause
Date
Msg-id e7f9235d0904201702q1a1a33d0k9f9845ba17bbc09f@mail.gmail.com
Whole thread Raw
In response to Re: Doubt about join clause  (jc_mich <juan.michaca@paasel.com>)
Responses Re: Doubt about join clause
List pgsql-general
On Mon, Apr 20, 2009 at 7:39 PM, jc_mich <juan.michaca@paasel.com> wrote:
>
> 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

select clients.id as client_id, (select stores.id from stores order by
(power(clients.x-stores.x)+power(clients.y-stores.y)) asc limit 1) as
store_id from clients;

Should do the trick, or at least something very similar.

--
- David T. Wilson
david.t.wilson@gmail.com

pgsql-general by date:

Previous
From: jc_mich
Date:
Subject: Re: Doubt about join clause
Next
From: Adrian Klaver
Date:
Subject: Re: round behavior differs between 8.1.5 and 8.3.7