Date: Wed, 24 Feb 2010 14:34:48 +0100
From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: Re: join with an array
Message-ID: <20100224133447.GA18435@apartia.fr>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <20100224124023.GA16500@apartia.fr>
	<20100224130909.GL22249@a-kretschmer.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20100224130909.GL22249@a-kretschmer.de>
User-Agent: Mutt/1.5.20 (2009-06-14)

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 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;