PostgreSQL query that retrieves an aggregated list

Question

I have four tables Users, Roles, User_Role, and Email. I need to develop a query that retrieves the user information (username, lastname, and firstname), the users email address (from the email table) and an aggregated list of all the roles (which is connected to the User tables with a many-to-many table user_role). I developed the query below which has one problem. The aggregated list has duplicate role name entries.

SELECT DISTINCT ON (u.username)
    u.username AS "Username", u.lastname AS "Last Name", u.firstname AS "First Name", e.name AS "Email Address"
    , 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 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
GROUP BY u.username, u.lastname, u.firstname, e.name
ORDER BY u.username

If I comment out the Inner email join and associated email fields (see below) I get the correct role names in the string_agg.

SELECT DISTINCT ON (u.username)
    u.username AS "Username", u.lastname AS "Last Name", u.firstname AS "First Name",
--  e.name AS "Email Address"
    , 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 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
GROUP BY u.username, u.lastname, u.firstname, 
--  e.name
ORDER BY u.username

The `inner join` means that users with there roles must have email. if they have not email, they are note selected. — Houari, Apr 08 '13 at 20:48
You forgot to supply your Postgres version and your table definitions. Which columns are `PRIMARY KEYS`? Which are `UNIQUE`? Which can be `NULL`? — Erwin Brandstetter, Apr 08 '13 at 22:34
This is most likely a case of "cross join by proxy". [This might help you.](http://stackoverflow.com/a/12464135/939860) — Erwin Brandstetter, Apr 08 '13 at 22:43

score 0 · Accepted Answer · answered Apr 08 '13 at 23:06

This would probably solve the problem. It's an educated guess. Not enough information in the question.

SELECT DISTINCT ON (u.username)
        u.username AS "Username"
      , u.lastname AS "Last Name"
      , u.firstname AS "First Name"
      , e.emails AS "Email Addresses"
      , string_agg(r.name, ';'  ORDER BY r.name) AS "Roles"
FROM    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, ', ') AS emails
   FROM   email
   GROUP  BY 1
   ) e  ON e.user_id = u.id
WHERE  u.active = 1
AND    r.name  ''
AND    r.active = 1
AND    ur.active = 1
GROUP  BY u.username, u.lastname, u.firstname, e.emails 
ORDER  BY u.username

Aside: r.name <> '' does the same as length(r.name) > 0, just faster.

Whether or not this should be a LEFT JOIN for emails instead of a JOIN is an unrelated question.
But I guess it should be, so I changed that, too.

@RWBear: Cool. :) Be so kind and give your question a proper title, so other people know what's it about. — Erwin Brandstetter, Apr 09 '13 at 00:49

PostgreSQL query that retrieves an aggregated list

1 Answers1