Lookup rows using pairs of values and find exact matching groups-CodePudding

I have these tables:

Project

id	name	version
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

Person

id	name
1	Jake
2	Skye
3	Kieth
4	Jim
5	Eliz

Person_Project

id	person_id	project_id
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

Persons will have unique projects, means no two persons will work on the same projects.

I am writing a java based api where I receive a json request with different projects and I have to return the person who is working on exact projects given in request.

Request:

[
    {"name": "Pete", "version": "0.0.1"}
]

This should return Jake

Request:

[
    {"name": "Pete", "version": "0.0.1"},
    {"name": "Swag", "version": "0.0.1"}
]

This should return Skye

Request:

[
    {"name": "Pete", "version": "0.0.1"},
    {"name": "Swag", "version": "0.0.2"}
]

This should return Kieth

I am writing SQL for this and not getting what I needed.

This is what I am up to

SELECT pe.id, pe.name
FROM person pe
LEFT JOIN person_project pepr on pepr.person_id = pe.id
WHERE pe.id IN (
    SELECT pepr.person_id
    FROM project pr
    LEFT JOIN person_project pepr ON pepr.project_id = pr.id
    WHERE pr.name IN ('Pete', 'Swag') AND pr.version IN ('0.0.1', '0.0.2')
    GROUP BY pepr.project_id
    HAVING COUNT(pepr.project_id) = 2
)
GROUP BY pe.id, pe.name
HAVING COUNT(pe.id) = 2

This is not right as I am using IN for versions which will gets applied to other projects.

CodePudding user response：

You can use SQL relational division logic as described in this answer. You're interested in the part that says exact division/no remainder:

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)

DB<>Fiddle for all three examples