Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Project (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  version VARCHAR(10) NOT NULL
);

CREATE TABLE


INSERT INTO Project (name, version)
VALUES
  ('Swam', '0.0.1'),
  ('Dinali', '0.0.1'),
  ('Dinali', '0.0.2'),
  ('BigR', '0.0.3'),
  ('Kale', '0.0.1'),
  ('Kale', '0.0.2');

INSERT 0 6


CREATE TABLE Person (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE


INSERT INTO Person (name)
VALUES
  ('Jake'),
  ('Skye'),
  ('Kieth'),
  ('Jim'),
  ('Eliz');

INSERT 0 5


CREATE TABLE Person_Project (
  id SERIAL PRIMARY KEY,
  person_id INTEGER NOT NULL,
  project_id INTEGER NOT NULL
);

CREATE TABLE


INSERT INTO Person_Project (person_id, project_id)
VALUES
  (1, 1),
  (2, 1),
  (2, 2),
  (3, 1),
  (3, 2),
  (4, 1),
  (4, 4),
  (5, 1),
  (6, 1),
  (6, 2),
  (6, 3);

INSERT 0 11


 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)


person_id


1

2

3

5



SELECT 4