CODEWITHSUNDEEP
mysqlsql
JeffW
JeffW

Reputation: 186

MySQL LIKE and NOT LIKE is missing a record

I have a table named platform with a column named entityid. The data in entityid is supposed to adhere to the format n.n.n (where n = a number of 1 or more numerals, the first number is a site ID).

If i run this query:

SELECT count(*) FROM platform

I get: 16063 So I've got 16063 rows in my table. When I try to filter for only site 18 I run this query:

SELECT count(*) FROM platform
where entityid like '18.%.%'

I get: 4454 So far, so good. But if I try to find platforms not at site 18:

SELECT count(*) FROM platform
where entityid not like '18.%.%'

I get: 11608 Here's the problem: 4454 + 11608 = 16062
I'm missing a record. I think I'm getting all the platforms that are at site 18, and then all the platforms that are not at site 18 - how am I missing one record?

Upvotes: 3

Views: 267

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

The problem is probably a null value. Try this and see if it returns a record:

select *
from platform
where entityid is null;

NULL values fail almost all comparisons (except for is null).

Upvotes: 3

Related Questions