I have to join user_name from user table where as first_name and last_names from user_profile table. Until here everything is fine, but when I try to fetch respective roles assigned from user_role tables it gives multiple rows for single user as 1 user can have multiple roles.
While trying to apply string_agg on role.names (so that multiple roles shown comma separated in single tuple), it gives each role in separate row.
Here is example query I am trying to run in postgresql:
SELECT users.user_name, user_profiles.first_name, user_profiles.last_name,
(
SELECT string_agg (roles.name, ',')
from roles
where roles.id in (
select user_roles.role_id where users.id = user_roles.user_id
)
) as name
FROM users
JOIN user_profiles ON users.id = user_profiles.user_id
JOIN user_roles ON user_roles.user_id = users.id
You must use GROUP BY clause in order to aggregate more than one record together within a group. That along with the unnecessary (I believe) nested SQL is leading you to wrong results.
Instead consider the following:
SELECT users.user_name, user_profiles.first_name, user_profiles.last_name,
string_agg (roles.name, ',') as name
FROM users
JOIN user_profiles
ON users.id = user_profiles.user_id
JOIN user_roles
ON user_roles.user_id = users.id
JOIN roles ON user_roles.role_id = roles.id
GROUP BY users.user_name, user_profiles.first_name, user_profiles.last_name