SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table project(id int, name varchar(10), version varchar(10));
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');
6 rows affected

create table person_project(id int, person_id int, project_id int);
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);
10 rows affected

with project_list as (
    select id
    from project
    where exists (
        select *
        from (values
            ('pete', '0.0.1'),
            ('swag', '0.0.1')
        ) as user_input(name, version)
        where project.name = user_input.name and project.version = user_input.version
    )
), person_project_copy as (
    select person_id, case when project_list.id is not null then 1 end as is_required
    from person_project
    left join project_list on person_project.project_id = project_list.id
)
select person_id
from person_project_copy
group by person_id
having count(is_required) = (select count(*) from project_list)
and    count(*)           = (select count(*) from project_list)


person_id


2


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


with project_list as (
    select id
    from project
    where exists (
        select *
        from (values
            ('pete', '0.0.1')
        ) as user_input(name, version)
        where project.name = user_input.name and project.version = user_input.version
    )
), person_project_copy as (
    select person_id, case when project_list.id is not null then 1 end as is_required
    from person_project
    left join project_list on person_project.project_id = project_list.id
)
select person_id
from person_project_copy
group by person_id
having count(is_required) = (select count(*) from project_list)
and    count(*)           = (select count(*) from project_list)


person_id


1


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


with project_list as (
    select id
    from project
    where exists (
        select *
        from (values
            ('pete', '0.0.1'),
            ('swag', '0.0.2')
        ) as user_input(name, version)
        where project.name = user_input.name and project.version = user_input.version
    )
), person_project_copy as (
    select person_id, case when project_list.id is not null then 1 end as is_required
    from person_project
    left join project_list on person_project.project_id = project_list.id
)
select person_id
from person_project_copy
group by person_id
having count(is_required) = (select count(*) from project_list)
and    count(*)           = (select count(*) from project_list)


person_id


3


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