Aggregate all joined rows with where clause

Question

Three nested tables exist: user_groups, users, projects. Each user_group has many users and, in turn, each user has many projects.

Each project has an ordinal assigned, 1-5 (step_number).

I would like to provide a summary for each user group. Each user group summary should include the users' projects and their unique progress of the users' projects and the steps they are at. Importantly, I would like to also like to select users projects with projects for a specific ordinal step. In other words, I would like to include the sibling projects for a users' steps.

For example, below, I would like all users aggregated by user_groups.id with projects that are step 3.

+------------+---------------------------------+
| User Group | Summary (Project Name: 1, 2, 5) |
+------------+---------------------------------+
|          1 | proj1: 2, 3, 5; proj2: 3, 5…    |
|          2 | proj33: 2, 3; proj 44: 1, 3     |
|          3 | proj55: 2, 3, 4; proj 66: 1, 3  |
+------------+---------------------------------+

The initial instinct was to write a query like this:

SELECT
  user_groups.id,
  STRING_AGG( DISTINCT(projects.step_number), ', ') AS summary
FROM user_groups
INNER JOIN users ON user_groups.id = users.user_group_id
LEFT JOIN projects ON users.id = projects.user_id
WHERE step_number = 3
GROUP BY user_group.id

However, that only returns projects where the step number is 3 and not the other steps for that user's other project. That makes sense, but how do I include all the sibling projects for that user_id?

I also tried different joins, but I am guessing and clearly unclear on how to solve this problem with SQL.

Here are some simplified table definitions

                                Table "public.user_groups"
  Column    |            Type             |                       Modifiers
------------+-----------------------------+-------------------------------------------------------
 id         | integer                     | not null default nextval('user_groups_id_seq'::regclass)
 created_at | timestamp without time zone |

                           Table "public.users"
   Column      |            Type             |                       Modifiers
---------------+-----------------------------+-------------------------------------------------------
 id            | integer                     | not null default nextval('users_id_seq'::regclass)
 user_group_id | integer                     |
 gender        | character varying(255)      |
 created_at    | timestamp without time zone |
 first_name    | character varying(255)      |
 last_name     | character varying(255)      |

                                  Table "public.projects"
   Column     |            Type             |                           Modifiers
--------------+-----------------------------+-------------------------------------------------------
 id           | integer                     | not null default nextval('projects_id_seq'::regclass)
 user_id      | integer                     |
 step_number  | integer                     |
 created_at   | timestamp without time zone |
 updated_at   | timestamp without time zone |
 start_dt     | date                        |
 end_dt       | date                        |
 project_name | text                        |

Answer 1

If I understand your requirements, this consists of selecting all the users that have a project in step 3, and using that list to select all the projects for just those users.

Something like:

SELECT ug.id
     , STRING_AGG(DISTINCT p.step_number, ', ') AS summary
FROM user_groups ug
    INNER JOIN (
            SELECT u.user_group_id
                 , u.user_id
            FROM users u
                INNER JOIN projects p ON u.user_id = p.user_id
            WHERE p.step_number = 3
            GROUP BY u.user_group_id, u.user_id
        ) Step3Projects ON ug.user_group_id = Step3Projects.user_group_id 
    INNER JOIN projects p ON Step3Projects.user_id = p.user_id
GROUP BY ug.id;

Answer 2

You may try to move your WHERE clause in the LEFT JOIN :

SELECT user_groups.id, STRING_AGG( DISTINCT(projects.step_number), ', ') AS summary
FROM user_groups
INNER JOIN users ON user_groups.id = users.user_group_id
LEFT JOIN projects ON users.id = projects.user_id AND step_number = 3
GROUP BY user_group.id

By doing this, you will not loose projects where step_number is not 3.