Complex 'Not Exists' Query - SQL Server 2005

[Evie]

Hi! I am trying to find some results NOT in a resultset

The query I am using to get the resultset:

SELECT     
     BD.id, 
     BD.a_id, 
     CONVERT(varchar, A.agent_id) + ' ' + A.fname + ' ' + A.lname AS name,   
     BD.b_id, 
     BD.agent_id, 
     BD.inactive, 
     BD.english, 
     BD.french, 
     BD.province
FROM Univlicval_billing_digits AS BD INNER JOIN
     Univlicval_agents AS A ON BD.a_id = A.id
WHERE
     (BD.b_id = 3) AND (BD.province = 'AB')

This gives me 5 results.

I want to get the rest of the agents that are not already assigned to the b_id AND province.

I run this query:

SELECT DISTINCT id, agent_id, fname, lname, inactive
FROM         Univlicval_agents AS A
WHERE     (NOT EXISTS
                          (SELECT     a_id
                            FROM          Univlicval_billing_digits AS BD
                            WHERE      (A.id = a_id) AND (b_id = 3) AND (province = 'AB')))

And get a complete resultset from agents (24 rows) where I should have 18 rows.

I know I'm missing something simple, but I can't figure out what it is. Thanks in advance!

Thanks in advance!

[WolfShade]

SELECT DISTINCT id, agent_id, fname, lname, inactive
FROM         Univlicval_agents AS A
WHERE     id NOT IN (
                          (SELECT     a_id
                            FROM          Univlicval_billing_digits AS BD
                            WHERE      (A.id = a_id) AND (b_id = 3) AND (province = 'AB')))

Should do it.

Except I just noticed - this probably won't work, as this is looking to select records where id's are not in a select that specifically looks for the id's you are not looking for.

I think.

Hmm...

[Evie]

That is correct, that query brought back all 24 records.

[eval(BadCode)]

Someone told me that MSSQL 2005 will eventually truncate

WHERE ? NOT IN ( ... )

Any truth to that?