NOT EXISTS Using IN

Transact-SQL
1

This is not a question about the difference between NOT EXISTS and IN.
I just can't seem to get this sql working right.
The logic seems ok, but I'm missing something.

One table named CompanyAccountantRef has 3 fields. ID, CompanyID, and AccountantID.
Currently in the table:

ID    CompanyID    AccountantID    
8       6706          346388    
9       6706          346256    
10      6706          26263    
11      363392        358951

Then this sql is not bringing back the correct row:

DECLARE @CompanyID INT = 363392
DECLARE @AccountIDs TABLE (ID INT)
INSERT INTO @AccountIDs (ID) VALUES (358951)
INSERT INTO @AccountIDs (ID) VALUES (26263)

SELECT @CompanyID AS CompanyID, a.ID 
FROM @AccountIDs a
WHERE NOT EXISTS(
SELECT *
FROM CompanyAccountantRef
WHERE CompanyID = @CompanyID 
AND AccountantID IN (SELECT ID FROM @AccountIDs))

It should bring back

 CompanyID    AccountantID
    363392       26263

What am I missing here? Is it the use of the IN that breaks it?
I've tried several different way including joins with no luck.

Thanks.

2

Hmm, in the data you're showing, accountant id 26263 is for company id 6706 rather than for company id 363392.

3

Yes, an accountant id can have more than one company.

4

Sorry, mis-read the q initially.

The query with NOT EXISTS() doesn't return any rows because in a fact a row does exist that meets the WHERE conditions specified:
11 363392 358951

Therefore, the "WHERE NOT EXISTS()" is not proven true, so no rows are selected.

Here's a similar example with only static data:

SELECT 1
WHERE NOT EXISTS(
    SELECT 1 WHERE 1 IN (1, 2, 3)
)
5 
-- *** Test Data ***
CREATE TABLE #CompanyAccountantRef
(
    ID int NOT NULL PRIMARY KEY
    ,CompanyID int NOT NULL
    ,AccountantID int NOT NULL
);
INSERT INTO #CompanyAccountantRef
VALUES (8, 6706, 346388) 
    ,(9, 6706, 346256)
    ,(10, 6706, 26263)
    ,(11, 363392, 358951);
CREATE TABLE #AccountIDs
(
    ID int NOT NULL PRIMARY KEY
);
INSERT INTO #AccountIDs
VALUES (358951), (26263);
-- *** End Test Data ***

DECLARE @CompanyID int = 363392;

SELECT @CompanyID AS CompanyID, C.AccountantID
FROM #CompanyAccountantRef C
    JOIN #AccountIDs A
        ON C.AccountantID = A.ID
WHERE C.CompanyID <> @CompanyID;

SELECT @CompanyID AS CompanyID, C.AccountantID
FROM #CompanyAccountantRef C
WHERE C.CompanyID <> @CompanyID
    AND EXISTS
    (
        SELECT 1
        FROM #AccountIDs A
        WHERE C.AccountantID = A.ID
    );

SELECT @CompanyID AS CompanyID, C.AccountantID
FROM #CompanyAccountantRef C
WHERE C.CompanyID <> @CompanyID
    AND C.AccountantID IN (SELECT A.ID FROM #AccountIDs A);
6

I would have written this differently, but I don't know if it makes the problem easier to read?

SELECT @CompanyID AS CompanyID, a.ID 
FROM @AccountIDs a
WHERE NOT EXISTS(
SELECT *
FROM CompanyAccountantRef
WHERE CompanyID = @CompanyID 
AND AccountantID IN (SELECT ID FROM @AccountIDs))

SELECT @CompanyID AS CompanyID, a.ID 
FROM @AccountIDs a
WHERE NOT EXISTS(
    SELECT *
    FROM CompanyAccountantRef AS CAR
         JOIN @AccountIDs AS A
             ON A.ID = CAR.AccountantID
    WHERE CAR.CompanyID = @CompanyID 
)