Thread: join with an array
Hi, I'm trying the following query: select array_agg(t1.id) from table1 t1 join table2 t2 on (t2.id = any(array_agg)) group by t1.col1; but I get this error: ERROR: column "array_agg" does not exist I tried aliasing array_agg(t1.id) without success. Thanks for any suggestions,
In response to Louis-David Mitterrand : > Hi, > > I'm trying the following query: > > select array_agg(t1.id) from table1 t1 join table2 t2 on (t2.id = any(array_agg)) group by t1.col1; > > but I get this error: ERROR: column "array_agg" does not exist > > I tried aliasing array_agg(t1.id) without success. > > Thanks for any suggestions, I can't really understand what you want to achieve, but maybe this is what you are looking for: test=*# select * from a;id | v ----+--- 1 | 1 2 | 1 3 | 1 4 | 1 5 | 2 6 | 2 (6 rows) test=*# select * from b;id ---- 1 3 (2 rows) test=*# select array_agg(a.id), v from a join b on (b.id in (select a.id from a)) group by a.v; array_agg | v -------------------+---{1,2,3,4,1,2,3,4} | 1{6,5,5,6} | 2 (2 rows) Question: you are 'pif' in the irc-channel? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On Wed, Feb 24, 2010 at 02:09:09PM +0100, A. Kretschmer wrote: > In response to Louis-David Mitterrand : > > Hi, > > > > I'm trying the following query: > > > > select array_agg(t1.id) from table1 t1 join table2 t2 on (t2.id = any(array_agg)) group by t1.col1; > > > > but I get this error: ERROR: column "array_agg" does not exist > > > > I tried aliasing array_agg(t1.id) without success. > > > > Thanks for any suggestions, > I can't really understand what you want to achieve, but maybe this is > what you are looking for: Here is a test case I built. I want to list all cruises by cruise_type but after merging cruise_type that have the same cruise_type_name: drop table cruise; drop table cruise_type; create table cruise_type ( id_cruise_type serial primary key, cruise_type_name text ); create table cruise ( id_cruise serial, id_cruise_type integer references cruise_type, cruise_date timestamp defaultnow() ); insert into cruise_type (cruise_type_name) values ('5 day eastern carribean cruise'), ('5 day western carribean cruise'), ('5 day eastern carribean cruise'), ('5 day western carribean cruise') ; insert into cruise (id_cruise_type) values (1), (2), (3), (4), (1), (2), (3), (4) ; select array_agg(ct.id_cruise_type),ct.cruise_type_name from cruise_type ct join cruise c on (c.id_cruise = any(array_agg))group by cruise_type_name;
Στις Wednesday 24 February 2010 15:34:48 ο/η Louis-David Mitterrand έγραψε: > Here is a test case I built. I want to list all cruises by cruise_type > but after merging cruise_type that have the same cruise_type_name: > > drop table cruise; > drop table cruise_type; > > create table cruise_type ( > id_cruise_type serial primary key, > cruise_type_name text > ); > > create table cruise ( > id_cruise serial, > id_cruise_type integer references cruise_type, > cruise_date timestamp default now() > ); > > insert into cruise_type (cruise_type_name) values > ('5 day eastern carribean cruise'), > ('5 day western carribean cruise'), > ('5 day eastern carribean cruise'), > ('5 day western carribean cruise') > ; > > insert into cruise (id_cruise_type) values > (1), > (2), > (3), > (4), > (1), > (2), > (3), > (4) > ; > > select array_agg(ct.id_cruise_type),ct.cruise_type_name from cruise_type ct join cruise c on (c.id_cruise = any(array_agg))group by cruise_type_name; > You dont specify (in english) what you exactly want to achive, but here is my shot: 1st, get the cruises by cruise type: select ct.id_cruise_type,array_agg(c.id_cruise) as "List of Cruises" from cruise_type ct, cruise c WHERE c.id_cruise_type=ct.id_cruise_typeGROUP BY ct.id_cruise_type ORDER BY ct.id_cruise_type;id_cruise_type | List of Cruises ----------------+----------------- 1 | {1,5} 2 | {2,6} 3 | {3,7} 4 | {4,8} (4 rows) test=# Then you may pretify this to include the name of each cruise type as well: select ct.id_cruise_type,ct.cruise_type_name,array_agg(c.id_cruise) as "List of Cruises" from cruise_type ct, cruise c WHEREc.id_cruise_type=ct.id_cruise_type GROUP BY ct.id_cruise_type,ct.cruise_type_name ORDER BY ct.id_cruise_type;id_cruise_type| cruise_type_name | List of Cruises ----------------+--------------------------------+----------------- 1 | 5 day eastern carribean cruise | {1,5} 2 | 5 day western carribean cruise | {2,6} 3 | 5 day eastern carribean cruise | {3,7} 4 | 5 day western carribean cruise | {4,8} (4 rows) EXERCISE: Why cant we exclude ct.id_cruise_type from the select clause and group by of the above query? -- Achilleas Mantzios