CODEWITHSUNDEEP
mysql
dranreb dino
dranreb dino

Reputation: 260

MySQL where clause not returning expected value

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?

Upvotes: 0

Views: 44

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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.

Upvotes: 1

Related Questions