Re: Double aggregate problem

Hello,

I have the following tables:

CREATE TABLE tblvacature (id serial PRIMARY KEY,account int NOT NULL REFERENCES tblaccount (id) ON DELETE CASCADE,
title varchar(128),bedrijfsprofiel text,functieomschrijving text,functieeisen text,arbeidsvoorwaarden textoverig
text,sollicitatieinfotext, 
inserted timestamp DEFAULT now()
);

CREATE TABLE tblvacaturesector ( vacature int NOT NULL REFERENCES
tblvacature (id) ON DELETE CASCADE, sector int NOT NULL REFERENCES
tblsector (id) ON DELETE CASCADE );
select * from tblvacaturesector where vacature = 11;vacature | sector
----------+--------      11 |      5      11 |      2

CREATE TABLE tblvacatureprovincie ( vacature int NOT NULL REFERENCES
tblvacature (id) ON DELETE CASCADE, provincie int NOT NULL REFERENCES
tblprovincie (id) ON DELETE CASCADE );
select * from tblvacatureprovincie where vacature = 11;vacature | provincie
----------+-----------      11 |         7      11 |         1      11 |         8

CREATE TABLE tblprovincie (id serial PRIMARY KEY,land int NOT NULL REFERENCES tblland (id) ON DELETE RESTRICT,
name varchar(128)
);
select * from tblprovincie;id | land |      name
----+------+----------------- 1 |    1 | Noord-Holland 2 |    1 | Zuid-Holland 3 |    1 | Groningen 4 |    2 | Brabant
5|    1 | Utrecht 6 |    2 | Antwerpen 7 |    2 | Limburg 8 |    2 | Oost-Vlaanderen 

CREATE TABLE tblsector (id serial PRIMARY KEY,
name varchar(128)
);
select * from tblsector;id |  name
----+--------- 4 | Aap 1 | Cool 5 | Eerder 2 | Gaafjes 6 | Later 3 | Netjes11 | ICT

I hope that's enough information?

> -----Oorspronkelijk bericht-----
> Van: Peter Eisentraut [mailto:peter_e@gmx.net]
> Verzonden: woensdag 22 juli 2009 20:05
> Aan: pgsql-sql@postgresql.org
> CC: David Weilers
> Onderwerp: Re: [SQL] Double aggregate problem
>
> On Wednesday 22 July 2009 19:16:21 David Weilers wrote:
> > I have the following query:
> >
> > select v.id, array_to_string(array_accum(s.name),', ') as sector ,
> > array_to_string(array_accum(p.name),', ') as provincie from
tblvacature
> > v, tblaccount a , tblvacaturesector vs, tblsector s ,
> > tblvacatureprovincie vp, tblprovincie p where v.id = 11 and
v.account =
> > a.id and vs.vacature = v.id and s.id = vs.sector and vp.vacature =
v.id
> > and p.id = vp.provincie group by v.id, v.inserted order by
v.inserted
> > desc
> >
> > That currently produces the following output:
>
> No one is going to be able to reproduce that without the table
definitions and
> data.
>