CODEWITHSUNDEEP
mysqlsql
Hyder
Hyder

Reputation: 1173

SQL Query not returning desired result with NOT LIKE

I am running a simple query where comparing a TEXT column using NOT LIKE but the results are not coming correct. Tried a lot but no luck. Here is the query :

SELECT *
FROM `restaurant_session_log`
WHERE `restId` = '176'
OR branchId = '203'
OR  `multi_vendorId` LIKE '%,176%' 
OR  `multi_vendorId` LIKE '%,176,%' 
OR  `multi_vendorId` LIKE '%176,%' 
OR  `multi_vendorId` LIKE '%[176]%' 
AND (`excluded_branch_id` NOT LIKE '%,203%'  OR `excluded_branch_id` NOT LIKE '%,203,%'  OR `excluded_branch_id` NOT LIKE '%203,%'  OR `excluded_branch_id` NOT LIKE '%[203]%' )

And here is the result: screenshot

Now the correct result would only include 2nd row, with id = 27707 because I have mentioned in the query to bring result where excluded_branch_id != %203% but I don't understand why it's giving the row with 203 in excluded_branch_id column.

Please help!

Upvotes: 0

Views: 82

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272256

You can use REGEXP to simplify the matching. The following matches 203 if it has word boundaries on both sides of it:

(excluded_branch_id IS NULL OR excluded_branch_id NOT REGEXP '[[:<:]]203[[:>:]]')

E.g:

SELECT
    '203,111' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '111,203' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '1,203,1' REGEXP '[[:<:]]203[[:>:]]', -- 1
    '1120311' REGEXP '[[:<:]]203[[:>:]]'  -- 0

Upvotes: 1

Ammar Iqbal
Ammar Iqbal

Reputation: 51

$query = "SELECT * FROM movies WHERE year_released NOT LIKE '200_';"

$result = db_query($query, array(':movies' => $value));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270473

Don't store multiple values in a single string! Don't put numeric values in a string! This is the root cause of your problems.

Sometimes, you cannot change someone's really, really, really bad data modeling decision. Your obvious problem is parentheses.

But, the logic would be simplified if you used find_in_set(). I think you intend:

WHERE (`restId` = '176' OR branchId = '203' OR
       find_in_set(176, `multi_vendorId`)
      ) AND
      (find_in_set(203, `excluded_branch_id`)

Upvotes: 1

Related Questions