Zath . Zath . - 7 months ago 12
SQL Question

NOT EXISTS returning no rows

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


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


And yes, an accountant can have more than one company.
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.

Answer

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.

Hence, the 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.