Reputation: 21
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.
Upvotes: 0
Views: 74
Reputation: 694
You may convert the data type of the Code column
select * from personsnumber where convert(nvarchar(50),code) != '9210'
Upvotes: 0
Reputation: 5403
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:
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.
Upvotes: 0
Reputation: 70528
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
Upvotes: 0
Reputation: 50173
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;
Upvotes: 1