I have two entities:
create table families (
family_id number primary key,
family_name varchar(100) not null
);
create table members (
member_id number primary key,
family_id number references family_id,
first_name varchar(100)
);
I'd like to display a list of families with a list of
members who are in each family. Something like this:
Campbell (Ryan, Wendy, Henry, Thomas)
What are the possible solutions to this? It would be
nice to be able to do something like this:
select family_name, '(' || join(select member_name
where family_id = f.family_id, ', ') || ')' as
family_members
from family f
The 'join' is meant in the sense of the PERL, TCL or
Javascript join function: taking a list of items and
appending them together into a string with a given
delimiter. Perhaps 'flatten' would be less confusing.
Is there already something like this out there? Would
it be possible to write something like it? Or is
there an existing method that allows me to achieve the
same result (without having to result to doing it by
hand in a stored proc or in my program)? I like this
solution because it's generic, but perhaps I'm taking
the wrong approach.
Thanks,
Ryan
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/