join returns too many results...

Поиск
Список
Период
Сортировка
От Frank Bax
Тема join returns too many results...
Дата
Msg-id BLU0-SMTP934181715239A633CC970AAC6B0@phx.gbl
обсуждение исходный текст
Ответы Re: join returns too many results...  (Joshua Tolley <eggyknap@gmail.com>)
Список 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...


В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Random sort with distinct
Следующее
От: Andreas
Дата:
Сообщение: Need magic for inserting in 2 tables