How to search for duplicates in one of the columns in MSSQL, provided that there is a match in the other column and there are no duplicates in the third?

Question

SQL

Colleagues, hello everyone!
There is a problem not from trivial.
Given a table:
id | project_id | sum_1 | sum_2
Task:
Find all records that match all of the following conditions:

1. The project_id field is the same within the same project_id:
   
2. The sum_2 field is duplicated 1 or more times
   
3. sum_1 field is NOT duplicated
   

Output such records in their entirety, that is:
id | project_id | sum_1 | sum_2 I've been struggling
with the task for four hours now, I can't do anything better than a cursor with row queries, but this is not at all the best way.

Answer 1

Try this option. I do not have access to your table and database, so I could only check with the first two conditions:

SELECT id, project_id, sum_1, sum_2
FROM [mytable] as tbl
WHERE 
sum_2 IN (
SELECT sum_2
 FROM [mytable]
 WHERE project_id = tbl.project_id
 GROUP BY  project_id, sum_2
HAVING count(sum_2) > 1)
AND project_id IN (
    SELECT project_id
    FROM [mytable]
    GROUP BY  project_id, sum_2
    HAVING count(sum_2) > 1)

In theory, this should work:

SELECT id, project_id, sum_1, sum_2
FROM [mytable] as tbl
WHERE 
sum_1 IN (
SELECT sum_1
 FROM [mytable]
 WHERE project_id = tbl.project_id
 GROUP BY  project_id, sum_1
HAVING count(sum_1) < 2)
sum_2 IN (
SELECT sum_2
 FROM [mytable]
 WHERE project_id = tbl.project_id
 GROUP BY  project_id, sum_2
HAVING count(sum_2) > 1)
AND project_id IN (
    SELECT project_id
    FROM [mytable]
    GROUP BY  project_id, sum_2
    HAVING count(sum_2) > 1)
AND project_id IN (
    SELECT project_id
    FROM [mytable]
    GROUP BY  project_id, sum_1
    HAVING count(sum_1) < 2)

You also need to take into account that the code is not optimized for execution on the production server due to the use of subqueries.

Answer 2

select 
t1.id ,  t1.project_id ,  t1.sum_1 ,  t1.sum_2
from table t1
inner join 
( select project_id , COUNT(sum_1) ac CT from  table group by project_id ) t2 ON t1.project_id=t2.project_id  and t2.CT>1
inner join 
( select project_id , COUNT(sum_2) ac CT from  table group by project_id ) t3 ON t1.project_id=t3.project_id  and t3.CT=1