I am new to SQL. 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.

>Solution :

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