[SQL] join returns too many results...
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...
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Need magic for inserting in 2 tables
Hi, I need to insert a lot of basically blank records into a table to be filled later. Sounds silly but please bear with me. :) projects ( project_id, project_name, ... ) companies ( company_id, ... ) departments ( department_id, department ) staff ( staff_id SERIAL, company_fk, department_fk, ... ) company_2_project ( project_fk, company_fk ) staff_2_project ( project_fk, staff_fk, project data, ... ) So with this I can store that company 99 belongs e.g. to project 3, 5 and 42 and staff_id 11, 13, 17 belongs to company 99. staff_2_project represents the connection of staff members to a project and holds projectrelated infos. Now say I have allready 100 companies out of the bigger adress pool connected to project 42 and I now want to add blank staffers out of department 40 and 50 linked with this project. I do step 1: insert into staff ( company_fk, ..., department_fk ) select company_fk, ..., department_fk from departments, companies, company_2_project AS c2p where company_id = c2p.company_fk and c2p.project_fk= 42 and department_id in ( 40, 50 ); step 2 would be to link those new blank staff records to project 42 by inserting a record into staff_2_project for every new staff_id. How can I find the new staff_ids while making sure I don't insert ids from other sessions? Is there an elegant way in SQL ? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need magic for inserting in 2 tables
On Sun, Oct 3, 2010 at 4:14 PM, Andreas wrote: > insert into staff ( company_fk, ..., department_fk ) > select company_fk, ..., department_fk > from departments, companies, company_2_project AS c2p > where company_id = c2p.company_fk > and c2p.project_fk = 42 > and department_id in ( 40, 50 ); > > step 2 would be to link those new blank staff records to project 42 by > inserting a record into staff_2_project for every new staff_id. > > How can I find the new staff_ids while making sure I don't insert ids from > other sessions? > Is there an elegant way in SQL ? Use returning? insert into . yada returning field1, field2, field3 -- To understand recursion, one must first understand recursion. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need magic for inserting in 2 tables
Am 04.10.2010 01:46, schrieb Scott Marlowe: On Sun, Oct 3, 2010 at 4:14 PM, Andreas wrote: insert into staff ( company_fk, ..., department_fk ) select company_fk, ..., department_fk from departments, companies, company_2_project AS c2p where company_id = c2p.company_fk and c2p.project_fk= 42 and department_id in ( 40, 50 ); step 2 would be to link those new blank staff records to project 42 by inserting a record into staff_2_project for every new staff_id. How can I find the new staff_ids while making sure I don't insert ids from other sessions? Is there an elegant way in SQL ? Use returning? insert into . yada returning field1, field2, field3 It seams the inserts can't be chained? :( The inner insert works when I run it separately but when I run the chained inserts I get an syntax error. How can a script use what RETURNING dumps out? I tried a bit but got nowhere. insert into staff_2_project ( staff_fk, project_fk ) insert into staff ( company_fk, ..., department_fk ) [...] returning staff_id, 42 as project_fk; and insert into staff_2_project ( staff_fk, project_fk ) ( insert into staff ( company_fk, ..., department_fk ) [...] returning staff_id, 42 as project_fk ) as s; and insert into staff_2_project ( staff_fk, project_fk ) select staff_id, project_fk from ( insert into staff ( company_fk, ..., department_fk ) [...] returning staff_id, 42 as project_fk ) as s; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need magic for inserting in 2 tables
On Sun, Oct 3, 2010 at 6:47 PM, Andreas wrote: > How can a script use what RETURNING dumps out? > I tried a bit but got nowhere. The same way it would use the output of a select, it's a record set. So it's x rows by y columns. -- To understand recursion, one must first understand recursion. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need magic for inserting in 2 tables
Am 04.10.2010 02:58, schrieb Scott Marlowe: The same way it would use the output of a select, it's a record set. So it's x rows by y columns. Then where were my insert statements wrong? Please, look this is a simple but complete example and show me my error. create temporary table table_1 ( id_1 serial primary key, txt text ); create temporary table table_2 as select 42::integer as id_2; ALTER TABLE table_2 ADD CONSTRAINT t2_pkey PRIMARY KEY( id_2 ); create temporary table t1_t2 ( fk_1 integer references table_1 ( id_1 ), fk_2 integer references table_2 ( id_2 ) ); -- delete from table_1; insert into t1_t2 ( fk_1, fk_2 ) insert into table_1 ( txt ) values ( 'A' ), ( 'B' ), ( 'C' ) returning id_1, 42; The inner insert works and dumps the inserted ids along with the constant which is needed in the outer insert as reference to the project. Both inserts run together give an error. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need magic for inserting in 2 tables
On Sun, Oct 3, 2010 at 8:31 PM, Andreas wrote: > > insert into t1_t2 ( fk_1, fk_2 ) > insert into table_1 ( txt ) > values ( 'A' ), ( 'B' ), ( 'C' ) > returning id_1, 42; > > The inner insert works and dumps the inserted ids along with the constant > which is needed in the outer insert as reference to the project. > > Both inserts run together give an error. Yeah, it's not capable of directly feeding the next insert like that. You run the first insert, get the results back, then cycle through them in your code to make the new inserts based on that. -- To understand recursion, one must first understand recursion. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
