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;