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.
And I have searched everywhere, even in my years and years of notes.
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
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(
WHERE CompanyID = @CompanyID
AND AccountantID IN (SELECT ID FROM @AccountIDs))
I'm not sure why you are confused. Consider the subquery:
SELECT * FROM CompanyAccountantRef WHERE CompanyID = @CompanyID AND AccountantID IN (SELECT ID FROM @AccountIDs)
This returns one row, because
CompanyAccountantRef has one matching row and its accountant id is
358951. There is a match in
@AccountIds. Hence, the query returns one row.
NOT EXISTS is false. And no rows are returned.
I suspect that you want a correlated subquery of some sort. I'm not sure what logic you are looking for. The logic you have doesn't seem particularly useful (either it returns all rows in
@AccountIds or none).
If I had to guess, you a looking for something like:
SELECT @CompanyID AS CompanyID, a.ID FROM @AccountIDs a WHERE NOT EXISTS (SELECT 1 FROM CompanyAccountantRef car WHERE car.CompanyID = @CompanyID AND car.AccountantID = a.ID );
At the very least, this returns the row you are looking for.