I have the following SQL query which I run against PgSQL 9.2:
-- SQL Query
Code:
SELECT DISTINCT ON (u.username)
	u.username AS "Username"
	, u.lastname AS "Last Name"
	, u.firstname AS "First Name"
	, e.name AS "Email Address"
	, ua.address1 AS "Address 1"
	, ua.address2 AS "Address 2"
	, ua.city AS "City"
	, ua.state AS "State"
	, ua.zip AS "Zip Code"
	, ua.country_id AS "Country ID"
	, u.work_phone AS "Work Phone"
	, u.home_phone AS "Home Phone"
	, u.mobile_phone AS "Mobile Phone"
	, u.fax AS "FAX"
	, u.pers_web AS "Personal Web"
	, u.bus_web AS "Work Web"
	, u.gender AS "Gender"
	, string_agg(r.name, ';' ORDER BY r.name) AS "Roles"
FROM 
	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 user_address ua ON u.id = ua.user_id
	INNER JOIN email e ON e.user_id = u.id
WHERE
	u.active = 1 
	AND length(r.name) > 0 
	AND r.active = 1 
	AND ur.active = 1
	AND u.username = 'adrianacunn@hotmail.com' -- testing
GROUP BY 
	u.username
	, u.lastname
	, u.firstname
	, e.name
	, ua.address1
	, ua.address2
	, ua.city
	, ua.state
	, ua.zip
	, ua.country_id
	, u.work_phone
	, u.home_phone
	, u.mobile_phone
	, u.fax
	, u.pers_web
	, u.bus_web
	, u.gender
ORDER BY
	u.username
If I run this quesy the the string_agg ends up with duplicate role names. If i remove the email INNER JOIN (like this)
Code:
SELECT DISTINCT ON (u.username)
	u.username AS "Username"
	, u.lastname AS "Last Name"
	, u.firstname AS "First Name"
	--, e.name AS "Email Address"
	, ua.address1 AS "Address 1"
	, ua.address2 AS "Address 2"
	, ua.city AS "City"
	, ua.state AS "State"
	, ua.zip AS "Zip Code"
	, ua.country_id AS "Country ID"
	, u.work_phone AS "Work Phone"
	, u.home_phone AS "Home Phone"
	, u.mobile_phone AS "Mobile Phone"
	, u.fax AS "FAX"
	, u.pers_web AS "Personal Web"
	, u.bus_web AS "Work Web"
	, u.gender AS "Gender"
	, string_agg(r.name, ';' ORDER BY r.name) AS "Roles"
FROM 
	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 user_address ua ON u.id = ua.user_id
	--INNER JOIN email e ON e.user_id = u.id
WHERE
	u.active = 1 
	AND length(r.name) > 0 
	AND r.active = 1 
	AND ur.active = 1
	AND u.username = 'adrianacunn@hotmail.com' -- testing
GROUP BY 
	u.username
	, u.lastname
	, u.firstname
	--, e.name
	, ua.address1
	, ua.address2
	, ua.city
	, ua.state
	, ua.zip
	, ua.country_id
	, u.work_phone
	, u.home_phone
	, u.mobile_phone
	, u.fax
	, u.pers_web
	, u.bus_web
	, u.gender
ORDER BY
	u.username
I get the corrent role names in the string_agg but, of course not the email address - Any help is greately appreciated