MySQL where clause not returning expected value

Question

I have a query

SELECT * 
FROM   hayabusa.customer_staging 
WHERE  tin = "888-596-592" 
        OR gsis_sss_no = "bp0279638" 
        OR last_name LIKE "%dela cruz%" 
        OR first_name LIKE "%jose%" 
        OR date_of_birth = "8/8/1978" 
        OR gender = "male" 
           AND parent_id = 0 
           AND is_proccessed = 0 
           AND id > 27 
           AND ( filename = "sample cif csv file - copy.csv" 
                  OR filename = "sample cif csv file2.csv" ) 
GROUP  BY tin, 
          gsis_sss_no, 
          last_name, 
          first_name, 
          date_of_birth, 
          gender 
ORDER  BY CASE 
            WHEN tin = "888-596-592" THEN 1 
            ELSE 2 
          end, 
          id 
LIMIT  1;

But it's returning a row with an id of 3 and a filename which is not in the where clause. What is wrong with it?

Always use parantheses when you use OR and AND. Try adding those and everything should be fine — Mihai Ovidiu Drăgoi
– Mihai Ovidiu Drăgoi, Commented Jun 6, 2017 at 5:47
Can you share the full record you don't think it is correct? — Zhong Dai
– Zhong Dai, Commented Jun 6, 2017 at 5:49

Tim Biegeleisen · Accepted Answer · 2017-06-06 05:48:22Z

I believe that AND has higher precedence than OR in MySQL (and most databases), so your current query is treating your AND condition with the filename as being optional. Try rewriting the WHERE clause as this:

WHERE
    (tin = "888-596-592" 
    OR gsis_sss_no = "bp0279638" 
    OR last_name LIKE "%dela cruz%" 
    OR first_name LIKE "%jose%" 
    OR date_of_birth = "8/8/1978" 
    OR gender = "male")
       AND parent_id = 0 
       AND is_proccessed = 0 
       AND id > 27 
       AND ( filename = "sample cif csv file - copy.csv" 
              OR filename = "sample cif csv file2.csv" )

Also, I don't know which columns you intend to select, but using SELECT * here is probably inappropriate. Instead, you should list out which columns you want to select. Strictly speaking, only columns appearing in the GROUP BY clause or columns which are inside aggregate functions are eligible for selection in your query.

Collectives™ on Stack Overflow

MySQL where clause not returning expected value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related