Retrieve row with LIKE and some id passed.

Question

I want to retrieve a row with keyword "off" and sc_id must be equal to "2". I am trying following query but it gives me the rows with keyword "Off" and also gives all the sc_id's. How to solve this. The id search is the must part.

Query

SELECT c.* , sc.* , sm.* ,ca.* 
from store_category sc 
INNER JOIN store_manufacture sm 
   ON sm.sm_id=sc.store_id 
INNER JOIN categories ca 
   ON ca.cat_id=sc.cat_id 
INNER JOIN coupons c 
   on c.c_sc_id=sc.sc_id 
WHERE sc.sc_id=2 
  AND c.c_name LIKE '%off%' 
   OR sm.sm_brand_name LIKE '%off%' 
   OR ca.cat_name LIKE '%off%' 
   OR c.c_description LIKE '%off%'

Well I mark as duplicated but now I wondering if your problem is because you use an `OR` so if any one is `off` you can have `Off` or any value on other field — Juan Carlos Oropeza, May 22 '17 at 19:27
No my question is totally different. It should provide me the keywords using "off" plus the sc_id is must. I am recieing all the rows with off keyword but my query is not considering sc_id field — tabia, May 22 '17 at 19:29
Group the `or`s `(c.c_name LIKE '%off%' OR sm.sm_brand_name LIKE '%off%' OR ca.cat_name LIKE '%off%' OR c.c_description LIKE '%off%')` As is you say `where sc.sc_id=2 AND c.c_name LIKE '%off%' ` OR `sm.sm_brand_name LIKE '%off%' OR ca.cat_name LIKE '%off%' OR c.c_description LIKE '%off%'` — chris85, May 22 '17 at 19:37

score 1 · Accepted Answer · answered May 22 '17 at 19:48

You need to use parenthesis for your conditions, try this:

SELECT c.* , sc.* , sm.* ,ca.* 
from store_category sc 
INNER JOIN store_manufacture sm 
ON sm.sm_id=sc.store_id 
INNER JOIN categories ca 
ON ca.cat_id=sc.cat_id 
INNER JOIN coupons c 
ON c.c_sc_id=sc.sc_id 
WHERE sc.sc_id=2 
AND (c.c_name LIKE '%off%' 
OR sm.sm_brand_name LIKE '%off%' 
OR ca.cat_name LIKE '%off%' 
OR c.c_description LIKE '%off%');

Retrieve row with LIKE and some id passed.

1 Answers1