Return results that don't have a specific value

Table info:

There are 2 columns personsnumber and their code.

a person can have multiple codes.

I was tasked to find people that don't have the 9210
When I write a statement WHERE code != 9210 it returns every person even if that person has 9210, but I only need the numbers that don't had 9210.

You can use not exists :

select t.*
from table t
where not exists (select 1 
                  from table t1 
                  where t1.personsnumber = t.personsnumber and t1.code = 9210
                );

Aggregation would also help you if only personsnumber is needed. :

select t.personsnumber
from table t
group by t.personsnumber
having sum(case when t.code = 9210 then 1 else 0 end) = 0;

You can use not exists (like Yogesh shows in their answer) or a left join (like this):

SELECT *
FROM sometable base
LEFT JOIN sometable code on base.personnumber = code.personnumber and code.code = 9210
WHERE code.personnumber is null

There's many, many ways to do this. I put some (not typing all of that from an image) data into a table variable:

DECLARE @MyTable TABLE (PersonNumber VARCHAR(50), Code VARCHAR(50));
INSERT INTO @MyTable SELECT '4050100', '9210';
INSERT INTO @MyTable SELECT '4050100', '1020';
INSERT INTO @MyTable SELECT '2650100', '1020';

Here's five ways to get the answer you want:

SELECT DISTINCT m1.PersonNumber FROM @MyTable m1 LEFT JOIN @MyTable m2 ON m2.PersonNumber = m1.PersonNumber AND m2.Code = '9210' WHERE m2.PersonNumber IS NULL;
SELECT DISTINCT PersonNumber FROM @MyTable m1 WHERE NOT EXISTS (SELECT * FROM @MyTable m2 WHERE m2.PersonNumber = m1.PersonNumber AND m2.Code = '9210');
WITH x AS (SELECT DISTINCT PersonNumber FROM @MyTable WHERE Code = '9210') SELECT DISTINCT m.PersonNumber FROM @MyTable m LEFT JOIN x ON x.PersonNumber = m.PersonNumber WHERE x.PersonNumber IS NULL;
SELECT PersonNumber FROM @MyTable GROUP BY PersonNumber HAVING MAX(CASE WHEN Code = '9210' THEN 1 END) IS NULL;
SELECT PersonNumber FROM @MyTable GROUP BY PersonNumber HAVING MAX(CASE WHEN Code = '9210' THEN 1 ELSE 0 END) = 0;

Briefly, here's how they work:

• LEFT JOIN to check there's no record with the 9210 code for the same person number
• NOT EXISTS - basically the same logic, but might be easier to follow?
• Using a common-table expression to identify the 9210s first, then find any cases where there's no match
• Using aggregation
• Using aggregation in an easier to follow query

I did some very, very rough analysis, and the aggregation methods seem to be slightly better for performance than the other methods, which are all about equal.

You may convert the data type of the Code column

select * from personsnumber where convert(nvarchar(50),code) != '9210'