LIKE query not working with WHERE and multiple OR WHERE

Question

I have join statements with WHERE and multiple OR WHERE with LIKE, below is what I've tried and unfortunately it's not working like even with invalid LIKE value e.g. LIKE %0000% and I'm sure there's no such relevant data on the given like query but still the sql below returns rows. Any help, ideas is greatly appreciated.

SELECT `a`.`id`, `a`.`subcategory_id`, `a`.`ps_id`, `b`.`id` AS `b_id`, `b`.`ps_keyword`, `b`.`created_at`
FROM `buss_subcategory_products_services` `a`
LEFT JOIN `buss_products_services` `b` ON `b`.`id` = `a`.`ps_id`
WHERE `b`.`is_deleted` =0
AND `a`.`subcategory_id` = '11'
OR   (
`a`.`subcategory_id` = '1'
OR `a`.`subcategory_id` = '8'
OR `a`.`subcategory_id` = '5'
OR `a`.`subcategory_id` = '2'
OR `a`.`subcategory_id` = '3'
OR `a`.`subcategory_id` = '9'
OR `a`.`subcategory_id` = '4'
OR `a`.`subcategory_id` = '10'
OR `a`.`subcategory_id` = '6'
OR `a`.`subcategory_id` = '7'
 )
AND  `b`.`ps_keyword` LIKE '%xxxxxxxxxxxxxxxxxxxxxxxxxxxx%' ESCAPE '!'
ORDER BY `b`.`ps_keyword` ASC

Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jun 16 '20 at 11:39
LEFT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. After a LEFT JOIN ON, a WHERE or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. It's a faq: [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) — philipxy, Jun 16 '20 at 11:40

score 2 · Answer 1 · answered Jun 16 '20 at 11:32

This looks like a logical prescendence issue.

This:

WHERE 
    `b`.`is_deleted` =0
    AND `a`.`subcategory_id` = '11'
    OR  (
        `a`.`subcategory_id` = '1'
        OR `a`.`subcategory_id` = '8'
        OR `a`.`subcategory_id` = '5'
        OR `a`.`subcategory_id` = '2'
        OR `a`.`subcategory_id` = '3'
        OR `a`.`subcategory_id` = '9'
        OR `a`.`subcategory_id` = '4'
        OR `a`.`subcategory_id` = '10'
        OR `a`.`subcategory_id` = '6'
        OR `a`.`subcategory_id` = '7'
    )
    AND `b`.`ps_keyword` LIKE '%xxxxxxxxxxxxxxxxxxxxxxxxxxxx%' ESCAPE '!'

Should probably be written:

WHERE 
    `b`.`is_deleted` =0
    AND (
        `a`.`subcategory_id` = '11'
        OR `a`.`subcategory_id` = '1'
        OR `a`.`subcategory_id` = '8'
        OR `a`.`subcategory_id` = '5'
        OR `a`.`subcategory_id` = '2'
        OR `a`.`subcategory_id` = '3'
        OR `a`.`subcategory_id` = '9'
        OR `a`.`subcategory_id` = '4'
        OR `a`.`subcategory_id` = '10'
        OR `a`.`subcategory_id` = '6'
        OR `a`.`subcategory_id` = '7'
    )
    AND `b`.`ps_keyword` LIKE '%xxxxxxxxxxxxxxxxxxxxxxxxxxxx%' ESCAPE '!'

I would recommend replacing these multiple OR conditions on the same column with IN:

WHERE
    `b`.`is_deleted` = 0
    AND `a`.`subcategory_id` IN (11, 1, 8, 5, ..., 7)
    AND `b`.`ps_keyword` LIKE '%xxxxxxxxxxxxxxxxxxxxxxxxxxxx%' ESCAPE '!'

Note that I changed the list of values to numbers instead of strings - these values look like numbers, so they should be stored and treated as such.

LIKE query not working with WHERE and multiple OR WHERE

1 Answers1