string_agg () is not working for Postgres sql when joining on multiple tables

Question

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

score 1 · Accepted Answer · edited Nov 16 '22 at 09:56

1

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

Online demo

edited Nov 16 '22 at 09:56

Zegarek

6,424
1
13
24

answered Nov 14 '22 at 19:55

JNevill

46,980
4
38
63

In Postgres, `GROUP BY users.id` is sufficient here, since that's the PK of the users table. – GMB Nov 14 '22 at 21:13
While I agree with the recommendation, both points in this answer are incorrect. @GMB I don't follow. It would throw `error: column "user_profiles.first_name" must appear in the GROUP BY clause or be used in an aggregate function` for each selected field outside aggregate functions. – Zegarek Nov 15 '22 at 06:50
while things are moving on, I am now struggling with having user_profiles table not mendatory. fetch user_profiles.first_name, user_profiles.last_name from user_profiles but if no data found show the results of respective user_id. ---- right now query dont show those user which has presence in user table but no respective data in profile table. – kah Nov 24 '22 at 14:11
got it solved by removing join for user_profiles and fetching profiles via inner query `SELECT users.id as user_id, users.is_active , roles.name as role_name, roles.id as role_id, ( SELECT user_profiles.first_name from user_profiles where user_profiles.user_id = users.id) as name FROM ums_users JOIN ums_user_roles ON ums_user_roles.user_id = ums_users.id`..... – kah Nov 24 '22 at 18:10

score 0 · Answer 2 · answered Nov 14 '22 at 20:38

You forgot a from in your innermost subquery:

SELECT  users.user_name, 
        user_profiles.first_name, 
        user_profiles.last_name,
        (SELECT string_agg (roles.name, ', ') 
         from  roles 
         where roles.id 
         in (   select u.role_id 
                from user_roles u --this was missing
                where users.id = u.user_id)) as name
FROM users 
    JOIN user_profiles 
        ON users.id = user_profiles.user_id
--  JOIN user_roles --you're already getting roles in the innermost query
--      ON user_roles.user_id = users.id;

without it, that subquery was just selecting one, current row's role_id, instead independently fetching of all roles for the user as you could've expected it to. See this demo.

Note that once this is fixed, you also need to get rid of the last join (commented out in my example) to avoid multiplying users for each of their roles - you're already getting their roles in the innermost subquery anyways.

It would be easier to use a regular group by:

select  users.user_name, 
        user_profiles.first_name, 
        user_profiles.last_name,
        string_agg(roles.name,', ')
from users 
    inner join user_profiles 
        on users.id=user_profiles.user_id
    inner join user_roles 
        on user_roles.user_id=users.id
    inner join roles 
        on roles.id=user_roles.role_id
group by 1,2,3;

string_agg () is not working for Postgres sql when joining on multiple tables

2 Answers2