SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Project (
  "id" INTEGER,
  "name" VARCHAR(100),
  "version" VARCHAR(100)
);

INSERT INTO Project
  ("id", "name", "version")
VALUES
  ('1', 'Pete', '0.0.1'),
  ('2', 'Swag', '0.0.1'),
  ('3', 'Swag', '0.0.2'),
  ('4', 'Swag', '0.0.3'),
  ('5', 'Kale', '0.0.1'),
  ('6', 'Kale', '0.0.2');

CREATE TABLE Person (
  "id" INTEGER,
  "name" VARCHAR(5)
);

INSERT INTO Person
  ("id", "name")
VALUES
  ('1', 'Jake'),
  ('2', 'Skye'),
  ('3', 'Kieth'),
  ('4', 'Jim'),
  ('5', 'Eliz');

CREATE TABLE Person_Project (
  "id" INTEGER,
  "person_id" INTEGER,
  "project_id" INTEGER
);

INSERT INTO Person_Project
  ("id", "person_id", "project_id")
VALUES
  ('1', '1', '1'),
  ('2', '2', '1'),
  ('3', '2', '2'),
  ('4', '3', '1'),
  ('5', '3', '3'),
  ('6', '4', '1'),
  ('7', '4', '4'),
  ('8', '5', '1'),
  ('9', '5', '2'),
  ('10', '5', '5');
21 rows affected

DECLARE @input TABLE (name varchar(100), version varchar(100), PRIMARY KEY (name, version));

INSERT @input (name, version) VALUES
('Pete','0.0.1');

SELECT
  p.name
FROM Person p
WHERE EXISTS (SELECT 1
    FROM Person_Project pp
    LEFT JOIN @input i
        JOIN Project prj ON prj.name = i.name AND prj.version = i.version
      ON pp.project_id = prj.id
    WHERE pp.person_id = p.id
    HAVING COUNT(prj.id) = COUNT(*)
       AND COUNT(prj.id) = (SELECT COUNT(*) FROM @input)
);


name


Jake


Warning: Null value is eliminated by an aggregate or other SET operation.


DECLARE @input TABLE (name varchar(100), version varchar(100), PRIMARY KEY (name, version));

INSERT @input (name, version) VALUES
('Pete','0.0.1'),
('Swag','0.0.1');

SELECT
  p.name
FROM Person p
WHERE EXISTS (SELECT 1
    FROM Person_Project pp
    LEFT JOIN @input i
        JOIN Project prj ON prj.name = i.name AND prj.version = i.version
      ON pp.project_id = prj.id
    WHERE pp.person_id = p.id
    HAVING COUNT(prj.id) = COUNT(*)
       AND COUNT(prj.id) = (SELECT COUNT(*) FROM @input)
);


name


Skye


Warning: Null value is eliminated by an aggregate or other SET operation.


DECLARE @input TABLE (name varchar(100), version varchar(100), PRIMARY KEY (name, version));

INSERT @input (name, version) VALUES
('Pete','0.0.1'),
('Swag','0.0.2');

SELECT
  p.name
FROM Person p
WHERE EXISTS (SELECT 1
    FROM Person_Project pp
    LEFT JOIN @input i
        JOIN Project prj ON prj.name = i.name AND prj.version = i.version
      ON pp.project_id = prj.id
    WHERE pp.person_id = p.id
    HAVING COUNT(prj.id) = COUNT(*)
       AND COUNT(prj.id) = (SELECT COUNT(*) FROM @input)
);


name


Kieth


Warning: Null value is eliminated by an aggregate or other SET operation.