CODEWITHSUNDEEP
mysql
Andelas
Andelas

Reputation: 2052

MySQL Query with multiple AND statements seems to be ignoring one

I'm trying to run a query on a MySQL database, but I found that it seems to be ignoring the 'status' item.

SELECT * FROM  `posts`  
WHERE
      `tags` LIKE '%gda%' 
   OR `tags` LIKE '%contests%' 
   OR `tags` LIKE '%merch%' 
  AND `newsID` != '2134' 
  AND `status` > '1' 
ORDER BY  `postDate` DESC  LIMIT 5

In that example, it's still pulling items even if 'status' is set to 0. What am I doing wrong?

Upvotes: 2

Views: 26690

Answers (5)

Samet Atdag
Samet Atdag

Reputation: 992

Try to use brackets to emphasize your logical sentences. Such as:

SELECT *  
  FROM  `posts`  
 WHERE  (`tags` LIKE  '%gda%' OR  `tags` LIKE  '%contests%' OR  `tags` LIKE  '%merch%') 
   AND  `newsID` !=  '2134' 
   AND `status` > '1' 
ORDER BY  `postDate` DESC  LIMIT 5

Otherwise, your logic gets lost. Also using brackets makes easier to read your SQL sentences.

Upvotes: 1

SWeko
SWeko

Reputation: 30902

Usually mixing ORs and ANDs is not a smart practice, since you'll get different results based on the execution order and the operator precedence, e.g, true or true and false, can be evaluated as (true or true) and false - yielding false or as true or (true and false) - yielding true.

Use parentheses to separate the ORs and the ANDs, and the execution order will be explicit, like this:

SELECT * FROM  `posts`
WHERE (`tags` LIKE '%gda%' OR `tags` LIKE '%contests%' OR `tags` LIKE '%merch%')
  AND `newsID` != '2134'
  AND `status` > '1'
ORDER BY  `postDate` DESC  LIMIT 5

Upvotes: 1

BvdVen
BvdVen

Reputation: 2961

Maybe you should use some brackets I don't know what combinations you want but try this:

  SELECT * FROM  `posts`  
    WHERE
         ( `tags` LIKE '%gda%' 
       OR `tags` LIKE '%contests%' 
       OR `tags` LIKE '%merch%' 
      )
      AND `newsID` != '2134' 
      AND `status` > '1' 
    ORDER BY  `postDate` DESC  LIMIT 5

Upvotes: 1

Sabeen Malik
Sabeen Malik

Reputation: 10880

have you tried something like:

SELECT * FROM  `posts`  
WHERE
     ( `tags` LIKE '%gda%' 
   OR `tags` LIKE '%contests%' 
   OR `tags` LIKE '%merch%')
  AND `newsID` != '2134' 
  AND `status` > '1' 
ORDER BY  `postDate` DESC  LIMIT 5

Upvotes: 1

eumiro
eumiro

Reputation: 212895

The problem is with the priority of OR/AND conditions. AND has a higher priority than OR, that's why it firstly evaluates all conditions connected by AND (tags-merch, newsID-2134 and status-1) and then evaluates the both tags-gda and tags-contests).

Try to add the brackets:

SELECT *  
  FROM  `posts`  
 WHERE (`tags` LIKE  '%gda%' 
    OR  `tags` LIKE  '%contests%' 
    OR  `tags` LIKE  '%merch%')
   AND  `newsID` !=  '2134' 
   AND `status` > '1' 
ORDER BY  `postDate` DESC
LIMIT 5

Upvotes: 14

Related Questions