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.