This is for a migration script.

CompanyTable:

In the below code I am Selecting duplicate EmployeeId-DivisionId combinations, that is, the records that have the same EmployeeId and DivisionId will be selected. So from the above table, the two rows that have abc-div1 combination will be selected by the below code.

How can I invert it? It seems so simple but I can't figure it out. I tried replacing with HAVING count(*) = 0 instead of > 1, I've tried fiddling with the equality signs in the ON and AND lines. Basically from the above table, I want to select the other three rows that don't have the abc-div1 combination. If there is a way to select all the unique EmployeeID-DivisionId combinations, let me know.

SELECT a.EmployeeID, a.DivisionId FROM CompanyTable a
  JOIN ( SELECT EmployeeID, DivisionId 
         FROM CompanyTable 
         GROUP BY EmployeeID, DivisionId 
         HAVING count(*) > 1 ) b
    ON a.EmployeeID = b.EmployeeID
   AND a.DivisionId = b.DivisionId;

EmployeeId and DivisionId are both nvarchar(50) columns.

CodePudding user response：

A windowed count would seem a suitable method:

select employeeid, divisionid
from (
    select *, Count(*) over(partition by employeeid, divisionid) ct
    from t
)t
where ct = 1;

CodePudding user response：

As already mentioned, you must replace > 1 by its real opposite <= 1, this works: db<>fiddle

CodePudding user response：

First, let's try rewriting your query using a common table expression (CTE), instead of a subquery:

WITH cteCompanyTableStats as (
    SELECT 
        EmployeeID, DivisionId, 
        HasDuplicates = CASE WHEN count(*) > 1 THEN1 ELSE 0 END
    FROM CompanyTable 
    GROUP BY EmployeeID, DivisionId 
)
SELECT ct.*
FROM CompanyTable ct
    inner join cteCompanyTableStats cts on
        ct.EmployeeId = cts.EmployeeId 
        and ct.DivisionId = cts.DivisionId
        and cts.HasDuplicates = 1

Notice how I've removed the HAVING clause & added a new HasDuplicates column? We're going to use that new column to find all of the table rows that -DON'T- have duplicates:

WITH cteCompanyTableStats as (
    SELECT 
        EmployeeID, DivisionId, 
        HasDuplicates = CASE WHEN count(*) > 1 THEN1 ELSE 0 END
    FROM CompanyTable 
    GROUP BY EmployeeID, DivisionId 
)
SELECT ct.*
FROM CompanyTable ct
    inner join cteCompanyTableStats cts on
        ct.EmployeeId = cts.EmployeeId 
        and ct.DivisionId = cts.DivisionId
        and cts.HasDuplicates = 0

The only character of SQL code that changed between the two queries was the last line, where and cts.HasDuplicates = ### is set.