pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin returns too many results...
2+ messages / 2 participants
[nested] [flat]
* join returns too many results...
@ 2010-10-03 18:54 Frank Bax <fbax@sympatico.ca>
2010-10-04 13:24 ` Re: join returns too many results... Joshua Tolley <eggyknap@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Frank Bax @ 2010-10-03 18:54 UTC (permalink / raw)
To: pgsql-sql
When I join tables; I will sometimes get multiple rows back as in this
example.
create table class(name varchar, p point, d int);
insert into class values( 'All', point(50,50), 100 );
insert into class values( 'NE70', point(70,70), 20 );
insert into class values( 'NE75', point(75,75), 20 );
insert into class values( 'NE80', point(80,80), 20 );
insert into class values( 'Centre', point(50,50), 5 );
create table list(x varchar, p point);
insert into list values('A',point(10,10));
insert into list values('B',point(75,75));
insert into list values('C',point(51,51));
select x,name,class.p<->list.p as dist, class.d as size from list left
join class on list.p <@ circle(class.p,class.d);
x | name | dist | size
---+--------+------------------+------
A | All | 56.5685424949238 | 100
B | All | 35.3553390593274 | 100
B | NE75 | 0 | 20
B | NE70 | 7.07106781186548 | 20
B | NE80 | 7.07106781186548 | 20
C | All | 1.4142135623731 | 100
C | Centre | 1.4142135623731 | 5
In the case where multiple rows are returned from class; I will need to
add another condition which decides which row to return.
1) distance from centre
2) size of circle
Whatever clause I choose to add I *must* ensure that final result set
contains only one-to-one join between tables. Either of these two
results is acceptable:
For option 1; result C=All or C=Centre is acceptable.
For option 2; result B=NE70 or B=NE75 or B=NE80 is acceptable.
I am lost trying to construct SQL to accomplish this...
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: join returns too many results...
2010-10-03 18:54 join returns too many results... Frank Bax <fbax@sympatico.ca>
@ 2010-10-04 13:24 ` Joshua Tolley <eggyknap@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Joshua Tolley @ 2010-10-04 13:24 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-sql
On Sun, Oct 03, 2010 at 02:54:41PM -0400, Frank Bax wrote:
> Whatever clause I choose to add I *must* ensure that final result set
> contains only one-to-one join between tables. Either of these two
> results is acceptable:
>
> For option 1; result C=All or C=Centre is acceptable.
> For option 2; result B=NE70 or B=NE75 or B=NE80 is acceptable.
>
> I am lost trying to construct SQL to accomplish this...
Try DISTINCT ON, after ensuring the results are ordered meaningfully.
http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-DISTINCT
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
Attachments:
[application/pgp-signature] signature.asc (196B, ../../4ca9d59a.26798e0a.1059.25cf@mx.google.com/2-signature.asc)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-10-04 13:24 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-03 18:54 join returns too many results... Frank Bax <fbax@sympatico.ca>
2010-10-04 13:24 ` Joshua Tolley <eggyknap@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox