From: | no(dot)spam(at)address(dot)com |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Denormalizing during select |
Date: | 2003-02-25 03:37:41 |
Message-ID: | m3pl5vsrmhi1rbn865huhqn29cjfmoal79@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Tue, 25 Feb 2003 01:22:42 +0000 (UTC), josh(at)agliodbs(dot)com (Josh
Berkus) wrote:
>Can you post the start of this thread? I think there's a more
>efficient way using custom aggregates, but somhow I never
>received the original question.
I'm hoping there is... somehow, using a function to loop over every
row seems a bit inefficient? Here's my original question...
I have two tables (A and B) in a one-to-many relationship. When
retrieving data from one table (A), I need to denormalize the tables
during a select so that values of a column in the many table (B)
appear as a list in one field of the output. E.g.:
create table one_table (
name varchar(20),
age integer,
--
constraint one_table_pk
primary key (name)
);
create table many_table (
name varchar(20),
property varchar(15),
--
constraint many_table_pk
primary key (name, property),
constraint many_table_fk
foreign key (name) references one_table (name)
);
So one_table could contain:
name age
---- ---
Mickey Mouse 50
Donald Duck 60
Goofy 45
and many_table could contain:
name property
---- --------
Mickey Mouse Small
Mickey Mouse Black
Mickey Mouse Squeaks
Donald Duck Quacks
The query I'm trying to write would have as output something like:
name age properties
---- --- ----------
Mickey Mouse 50 Small, Black, Squeaks
Donald Duck 60 Quacks
Goofy 45 <null>
Could somebody please help point me in the right direction? I imagine
this is a common need...
Thanks,
...Edmund.
From | Date | Subject | |
---|---|---|---|
Next Message | Edmund Lian | 2003-02-25 03:44:02 | Re: Denormalizing during select |
Previous Message | Josh Berkus | 2003-02-25 01:16:24 | Re: indexing |