postgresql - PostgresSQL query -

i have 4 tables users, roles, user_role, , email. need develop query retrieves user information (username, lastname, , firstname), users email address (from email table) , aggregated list of roles (which connected user tables many-to-many table user_role). developed query below has 1 problem. aggregated list has duplicate role name entries.

select distinct on (u.username)     u.username "username", u.lastname "last name", u.firstname "first name", e.name "email address"     , string_agg(r.name, ';'  order r.name) "roles"    users u     left outer join user_role ur on (u.id=ur.user_id)     left outer join roles r on (ur.role_id = r.id)     inner join email e on e.user_id = u.id   u.active = 1 , length(r.name) > 0 , r.active = 1 , ur.active = 1 group u.username, u.lastname, u.firstname, e.name order u.username 

if comment out inner email join , associated email fields(see below) correct role names in string_agg.

select distinct on (u.username)     u.username "username", u.lastname "last name", u.firstname "first name", --  e.name "email address"     , string_agg(r.name, ';'  order r.name) "roles"    users u     left outer join user_role ur on (u.id=ur.user_id)     left outer join roles r on (ur.role_id = r.id) --  inner join email e on e.user_id = u.id   u.active = 1 , length(r.name) > 0 , r.active = 1 , ur.active = 1 group u.username, u.lastname, u.firstname,  --  e.name order u.username 

any appreciated

this solve problem. it's educated guess. not enough information in question.

select distinct on (u.username)         u.username "username"       , u.lastname "last name"       , u.firstname "first name"       , e.emails "email addresses"       , string_agg(r.name, ';'  order r.name) "roles"    users u left    join user_role ur on u.id = ur.user_id left    join roles     r  on ur.role_id = r.id left    join (    select user_id, string_agg(name, ', ') emails      email    group  1    ) e  on e.user_id = u.id  u.active = 1 ,    r.name  '' ,    r.active = 1 ,    ur.active = 1 group  u.username, u.lastname, u.firstname, e.emails  order  u.username 

aside: r.name <> '' same length(r.name) > 0, faster.

whether or not should left join emails instead of join unrelated question.
guess should be, changed that, too.