CODEWITHSUNDEEP
mysqlsqlwhere-clause
Necro.
Necro.

Reputation: 987

MySQL - Where Or?

I have a MySQL statement where I'm trying to exclude statements depending on where they "belong to" The query goes through, but it still shows the rows that I specifically said where its not equal to?

SELECT id, belongsto, title, madeby, sticky, locked, lastpost, posts
FROM threads
WHERE sticky !=1
AND belongsto !=12 OR sticky !=1 AND belongsto !=13
ORDER BY  `threads`.`date` DESC 
LIMIT 20

Upvotes: 15

Views: 43536

Answers (2)

spencer7593
spencer7593

Reputation: 108370

If the goal is to exclude rows with belongsto values of 12 and 13, then the OR should be replaced with AND.

The assertion (in the selected answer) that parentheses are "missing" and need to be added, is wrong. The addition of parentheses does not change the change the statement. The AND operator already has a higher precedence than the OR operator.)

WHERE sticky !=1
AND belongsto !=12 AND sticky !=1 AND belongsto !=13
                   ^^^

Because it's unnecessary to repeat the same predicate twice, this could be rewritten as:

WHERE sticky !=1
AND belongsto !=12 AND belongsto !=13

Which can also be written as:

WHERE sticky !=1
  AND NOT (belongsto =12 OR belongsto =13)

This can also be rewritten using a NOT IN (12,13) (as demonstrated in the selected answer).

Upvotes: 3

John Conde
John Conde

Reputation: 219794

You need parenthesis around your OR statements to group them together logically.

WHERE sticky !=1
AND belongsto !=12 OR sticky !=1 AND belongsto !=13

should be:

WHERE (sticky !=1 AND belongsto !=12)
OR (sticky !=1 AND belongsto !=13)

or better yet:

 WHERE sticky !=1 AND belongsto NOT IN(12,13)

Upvotes: 35

Related Questions