I have these tables:
Project
| id | name | version |
|---|---|---|
| 1 | Swam | 0.0.1 |
| 2 | Dinali | 0.0.1 |
| 3 | Dinali | 0.0.2 |
| 4 | BigR | 0.0.3 |
| 5 | Kale | 0.0.1 |
| 6 | Kale | 0.0.2 |
Person
| id | name |
|---|---|
| 1 | Jake |
| 2 | Skye |
| 3 | Kieth |
| 4 | Jim |
| 5 | Eliz |
| 6 | Haun |
Person_Project
| id | person_id | project_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 2 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
| 7 | 4 | 4 |
| 8 | 5 | 1 |
| 9 | 6 | 1 |
| 10 | 6 | 2 |
| 11 | 6 | 3 |
I want to find all the persons who are working on same projects exact match. From the above data, result should be persons 1 and 5 because they both working on project 1, persons 2 and 3 because they are working on same projects 1 and 2.
Should not return 4 and 6 as there are no other persons working exact projects they are working on
>Solution :
Query:
with projects as
(
select person_id, STRING_AGG (project_id::varchar,' , ' order by project_id ) project_ids from Person_Project
group by Person_id
)
,cte as
(select project_ids from projects group by project_ids having count(*)>1)
select person_id from projects where project_ids in (select project_ids from cte)
OUtput:
| person_id |
|---|
| 1 |
| 2 |
| 3 |
| 5 |