many to many - Postgresql 9.3 - array_agg challenge -

many to many - Postgresql 9.3 - array_agg challenge -

i'm trying understand array_agg function in postgresql 9.3. i've set fun illustration may interested in participating.

any fan of american films 1980's may familiar "brat pack" appeared in many nail films together. using info brat pack films on wikipedia, i've created tables when joined together, can tell worked each other -- if have right query!

/* see: http://en.wikipedia.org/wiki/brat_pack_(actors) */ create table actor( id serial primary key, name varchar(50) ); insert actor(name) values ('emilio estevez'),('anthony michael hall'),('rob lowe'),('andrew mccarthy'),('demi moore'),('judd nelson'),('molly ringwald'),('ally sheedy') create table movie( id serial primary key, title varchar(200) ); insert movie(title) values ('the outsiders'),('class'),('sixteen candles'),('oxford blues'),('the breakfast club'),('st. elmos fire'), ('pretty in pink'),('blue city'),('about lastly night'),('wisdom'), ('fresh horses'),('betsys wedding'),('hail caesar'); create table movie_brats( id serial primary key, movie_id int references movie(id), actor_id int references actor(id) ); insert movie_brats(movie_id, actor_id) values (1,1),(1,3),(2,3),(2,4),(3,2),(3,7),(4,3),(4,8),(5,1),(5,2),(5,6), (5,7),(5,8),(6,1),(6,3),(6,4),(6,5),(6,6),(6,8),(7,4),(7,7),(8,6),(8,8),(9,3),(9,5),(10,1),(10,5),(11,4),(11,7), (12,7),(12,8),(13,2),(13,6);

query: show distinct list of each fellow member of brat pack worked with, ordered name in both columns

name worked ---------------------------------------------------------------------------------------------------------------- emelio estevez | emilio estevez, anthony michael hall, rob lowe, andrew mccarthy, demi moore, judd nelson, molly ringwald, ally sheedy */

my broken query:

select a1.name, array_to_string(array_agg(a2.name),', ') co_stars actor a1, actor a2, film m, movie_brats mb m.id = mb.movie_id , a1.id = mb.actor_id , a2.id = mb.actor_id grouping a1.id

sql fiddle

with v ( select a.id actor_id, a.name actor_name, m.id m_id actor inner bring together movie_brats mb on a.id = mb.actor_id inner bring together film m on m.id = mb.movie_id ) select v1.actor_name "name", string_agg( distinct v2.actor_name, ', ' order v2.actor_name ) "worked with" v v1 left bring together v v2 on v1.m_id = v2.m_id , v1.actor_id != v2.actor_id grouping 1 order 1

the distinct aggregation above necessary not show repeated names in case worked in more 1 movie.

the left join necessary not suppress actor did not work of others in list happen inner join.

if want show in film worked together: sql fiddle

with v ( select a.id actor_id, a.name actor_name, m.id m_id, m.title title actor inner bring together movie_brats mb on a.id = mb.actor_id inner bring together film m on m.id = mb.movie_id ) select a1 "name", string_agg( format('%s (in %s)', a2, title), ', ' order format('%s (in %s)', a2, title) ) "worked with" ( select v1.actor_name a1, v2.actor_name a2, string_agg(v1.title, ', ' order v1.title) title v v1 left bring together v v2 on v1.m_id = v2.m_id , v1.actor_id != v2.actor_id grouping 1, 2 ) s grouping 1 order 1

postgresql many-to-many aggregate-functions