Hi,
I am looking for some suggestions on my test example.
I have two tables: one for persons and the other for dependents table,
which is shown below. I would like to concatenate all children of the
same parents in a single string such as:
Children
-----------------
Kidc One
Kidf Two, Kidg Two
Can anyone help me how to do that from the following tables?
I have installed PostgreSQL 8.1.3.
The tables and their records are given below.
create table persons
(
person_id serial ,
fname varchar(32) ,
CONSTRAINT person_pk PRIMARY KEY(person_id)
);
create table dependents
(
parent_id integer not null,
child_id integer not null,
CONSTRAINT dependents_pk PRIMARY KEY(parent_id, child_id)
);
insert into persons(fname) values('Dada One');
insert into persons(fname) values('Momb One');
insert into persons(fname) values('Kidc One');
insert into persons(fname) values('Dadd Two');
insert into persons(fname) values('Mome Two');
insert into persons(fname) values('Kidf Two');
insert into persons(fname) values('Kidg Two');
insert into persons(fname) values('Dadh Three');
insert into persons(fname) values('Momi Three');
insert into persons(fname) values('Dadj Four');
insert into dependents(parent_id, child_id) values('1', '3');
insert into dependents(parent_id, child_id) values('2', '3');
insert into dependents(parent_id, child_id) values('4', '6');
insert into dependents(parent_id, child_id) values('5', '6');
insert into dependents(parent_id, child_id) values('4', '7');
insert into dependents(parent_id, child_id) values('5', '7');
The following attempt can list all the children but I'm not sure how to
group them into a single string based on their parents.
SELECT DISTINCT c.fname AS "Children"
FROM persons p, persons c, dependents d
WHERE d.parent_id = p.person_id AND
d.child_id = c.person_id
;
Children
----------
Kidc One
Kidf Two
Kidg Two
Thanks,
- Chansup