CODEWITHSUNDEEP
sqlsql-server
XavierR
XavierR

Reputation: 21

Return results that don't have a specific value

Table info:

enter image description here

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

Answers (4)

Gnyasha
Gnyasha

Reputation: 694

You may convert the data type of the Code column

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

Upvotes: 0

Richard Hansell
Richard Hansell

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:

  • 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.

Upvotes: 0

Hogan
Hogan

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

Yogesh Sharma
Yogesh Sharma

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

Related Questions