CODEWITHSUNDEEP
mysql
BB Design
BB Design

Reputation: 705

Select records from table where a given column value has no non-null values in another column

In this simple table example:

ID    SUBID
1000  NULL
1000  NULL
1000  1
1000  NULL
1001  NULL
1001  NULL

I would like my query to return an ID of 1001 only, because all 1001 IDs have NULL in SUBID. 1000 should be excluded, because at least one 1000 ID also has a non-NULL in SUBID.

So something like (convert my plain English to SQL):

select distinct id from table where all records with that id have NULL in subid

Upvotes: 1

Views: 674

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31772

Use a GROUP BY query and check that all SUBID entries are NULL using the BIT_AND() aggregate function:

select ID
from myTable
group by ID
having bit_and(SUBID is null)

Demo: https://www.db-fiddle.com/f/8dnfHV6VVVu7dvoZarTjdp/0

You can also replace the HAVING clause by 

having count(SUBID) = 0

since COUNT() will ignore all NULL entries.

Demo: https://www.db-fiddle.com/f/t3FrL7zUAwGqqWDS4dQUg9/0

This version should work for any major RDBMS.

Or

having max(SUBID) is null

This works with most aggregate functions, since they will return NULL, if all entries are NULL. (COUNT() is an exception.)

However - MAX() or MIN() might be the fastest, if you have an index on (ID, SUBID).

Upvotes: 1

Nick
Nick

Reputation: 147146

You could use a NOT EXISTS clause to check for ID values which have a non-NULL subid value and exclude them from your result:

SELECT DISTINCT m1.ID
FROM myTable m1
WHERE NOT EXISTS (SELECT * 
                  FROM myTable m2 
                  WHERE m2.ID = m1.ID AND m2.subID IS NOT NULL)

Alternatively, you could count the rows associated with the ID value and also count the number of NULL subid values associated with that ID, and see if they are the same:

SELECT ID
FROM myTable m1
GROUP BY ID
HAVING COUNT(*) = SUM(subid IS NULL)

Output:

1001

Demo on dbfiddle

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a NOT IN the subid where is null 

select distinct id 
from table 
where id NOT IN (
  select distinct id from table where subid is null  
)

Upvotes: 2

Related Questions