2

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                        |
2

If I understand you 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;
  • 1
    Thank you this worked well. And, yes, you put the requirement succinctly. – JHo Apr 16 '15 at 20:56
0

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.

  • Thanks for your answer. I got a lot of nulls when I did this but I am not sure why. The dataset I have does not have any nulls for the step_number. – JHo Apr 16 '15 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.