Lê Khánh Vinh Lê Khánh Vinh - 4 years ago 136
SQL Question

PhP MySQL NOT LIKE does not return the correct value

Hi Im trying to

Querry
Result using
LIKE
and
NOT LIKE
in
PHP-MySQL


I Have A table
BeaconEvent
consit of 2 colum:
BeaconID
and
EventID
;

Table BeaconEvent
BeaconID EventID
5DBA-BC5B Time0003
5DBA-BC5B NLB0001
5264-9028 NLB0004
1F8B-35E6 NLB0005


When I run the MySQL command to select all BeaconID with EventID is specific value, the query work fine but when I run the query to select BeaconID with EventID is not equal to specify value, It return all the BeaconID (Which Is not right, should exclude the beaconID which has Event ID equal to specific value)

Example query work:

SELECT BeaconEvent.BeaconID
FROM BeaconEvent
WHERE BeaconEvent.EventID = "Time0003"

SELECT BeaconEvent.BeaconID
FROM BeaconEvent
WHERE BeaconEvent.EventID LIKE "Time0003"


And these query does not work:

SELECT BeaconEvent.BeaconID
FROM BeaconEvent
WHERE BeaconEvent.EventID != "Time0003"

SELECT BeaconEvent.BeaconID
FROM BeaconEvent
WHERE BeaconEvent.EventID <> "Time0003"

SELECT BeaconEvent.BeaconID
FROM BeaconEvent
WHERE BeaconEvent.EventID NOT LIKE "Time0003"

SELECT BeaconEvent.BeaconID
FROM BeaconEvent
WHERE BeaconEvent.EventID NOT LIKE "%Time0003%"


Any suggestion to compare value which is not equal to specific one? thanks in advance

Answer Source

Your data has the same BeaconID for two values:

BeaconID        EventID
5DBA-BC5B       Time0003       
5DBA-BC5B       NLB0001   

Your queries work perfectly as they should:

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID <> "Time0003";

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID = "Time0003";

SqlFiddleDemo

Output:

╔═══════════╗
║ BeaconID  ║
╠═══════════╣
║ 5DBA-BC5B ║
║ 5264-9028 ║
║ 1F8B-35E6 ║
╚═══════════╝

╔═══════════╗
║ BeaconID  ║
╠═══════════╣
║ 5DBA-BC5B ║
╚═══════════╝

If you want to exclude it(and you are sure your data is correct) you can use:

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.BeaconID <> (SELECT BeaconID 
                               FROM BeaconEvent
                               WHERE EventID = 'Time0003')

SqlFiddleDemo2

Output:

╔═══════════╗
║ BeaconID  ║
╠═══════════╣
║ 5264-9028 ║
║ 1F8B-35E6 ║
╚═══════════╝
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download