Retrieving data in status of active with a wild card criteria (Mysql)

Question

I have problem retrieving data in database.

Question: Why I still retrieve the data of having store_status DELETED but my condition is store_status = 'Active' only

This is my script look like

SELECT * FROM store_locator WHERE store_status = 'Active' AND store_address LIKE '%Q%' 
OR branch_name LIKE '%Q%' OR city LIKE '%Q%'

And the sample image sample image

Answer 1

It's likely happening because either your branch_name has a Q in it or the city has a Q in it. The OR conditions ensure that records can still be shown even though the AND conditions are false. Your query is essentially running this:

SELECT *
FROM store_locator
WHERE (store_status = 'Active'
  AND store_address LIKE '%Q%')
OR (branch_name LIKE '%Q%')
OR (city LIKE '%Q%')

So if any of these conditions are true, the record will be shown:

• store_status = 'Active' AND store_address LIKE '%Q%'
• branch_name LIKE '%Q%'
• city LIKE '%Q%'

If this is not the logic you wanted, then you can adjust your query to include brackets () around the AND/OR criteria that you want to use. If you only want to see active records where any of those fields have a Q in it, you may want this query:

SELECT *
FROM store_locator
WHERE store_status = 'Active'
AND (store_address LIKE '%Q%'
OR branch_name LIKE '%Q%'
OR city LIKE '%Q%')

Note that the brackets have enclosed all of the three LIKE '%Q%' criteria in a single condition.

Answer 2

It's because you didn't group your OR condition,

IMO this should be your right query to use:

SELECT * 
FROM store_locator 
WHERE store_status = 'Active' 
    AND (store_address LIKE '%Q%' OR branch_name LIKE '%Q%' OR city LIKE '%Q%');