CODEWITHSUNDEEP
sqlsql-servert-sqlnullexists
Saif
Saif

Reputation: 19

Query to find null of specific column with multiple entries

Using my example below, I would like to find all the users that have a null value in a column even though they have multiple entries in that table. So for example I have a table like this:

ID userid col1 col2 col3
1 user1 a null b
2 user1 b a b
3 user2 a null b
4 user2 b null b

I would like to return user2 only since it has col2 that has nothing filled in for any of the rows. However, how do I create a query that will not bring me back user1 because user1 does have a value for col2 in one of the rows. User1 keeps getting pulled because it does have a row where col2 is null. Basically any userId that nothing populated in col2 in any of the rows in this table. If a userId does have a value in col2, don't return that userId.

I tried something like this:

select distinct(a.userid) from User a
group by a.userid 
having a.col is null

Upvotes: 1

Views: 997

Answers (2)

forpas
forpas

Reputation: 164064

Use NOT EXISTS:

SELECT DISTINCT u1.userid
FROM User u1
WHERE NOT EXISTS (SELECT 1 FROM User u2 WHERE u2.userid = u1.userid AND u2.col2 IS NOT NULL)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you just want the user, you can use aggregation:

select user
from t
group by user
having max(col1) is null or
       max(col2) is null or
       max(col3) is null;

MAX() (or most aggregation functions) return NULL only when all values are NULL. You can also use COUNT():

having count(col1) = 0 or
       count(col2) = 0 or
       count(col3) = 0;

Upvotes: 1

Related Questions