"not exist" does not display results?

SELECT a.samAccountName
FROM   activeIds AS a
WHERE  NOT EXISTS (SELECT *
                   FROM   #tmp1 AS b
                   WHERE  a.samAccountName = b.userID)
       A开发者_开发知识库ND a.samAccountName LIKE 'ysp%'
ORDER  BY a.samAccountName ASC;  
GO

I created a temp table that populates User IDs YSP0000 to YSP9999. I have an existing table (activeIds) that is already populated with YSP IDs.

I'm trying to output YSP IDs that DON'T exist in the existing table (activeIds) already.

For some reason the YSP IDs are not displaying and other IDs (for example ZSP) appear instead.

Is there a way to make the IDs appear?

I believe what you're looking for is not NOT EXISTS, but NOT IN. Something like this:

select samAccountName
from activeIds
where samAccountId not in
(
    select badAccountIds
    from #temp1
)

That will select all account names that IDs are not in the temp table.

You can accomplish the same thing with a left outer join.

select a.samAccountName
from activeIds a
   left outer join #tmp1 b on a.samAccountName = b.userID 
where b.userID is null  -- don't exist in #tmp1
      and a.samAccountName like 'ysp%'
order by a.samAccountName