CODEWITHSUNDEEP
mysql
Trombone0904
Trombone0904

Reputation: 4268

get mysql data where column has 2 values

I have the following mysql query:

SELECT * FROM `notifications` 
WHERE `receiverUserID` = 3 AND `status` = 0 AND `typ` = 1 OR `typ` = 2

the result:

enter image description here

But my query is not correct. The result should show me only data where typ = 1 OR 2, status = 0 and the receiverUserID = 3

the row where receiverUserID = 2 should not be shown. Where is my mistake ?

Upvotes: 0

Views: 54

Answers (2)

CodeTalker
CodeTalker

Reputation: 1791

You need to shorten down the scope of OR operator by using parenthesis. So your query should be 

SELECT * FROM notifications
WHERE receiverUserID = 3 AND status = 0 AND (typ = 1 OR typ = 2 );

Edit 1 (Helpful comment by @jBuchholz)

Here is a list of operator precedences in MySQL dev.mysql.com/doc/refman/8.0/en/operator-precedence.html AND has higher precedence than OR and is therefore executed earlier (like multiplication is executed before addition).

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521339

Here is what your query was actually doing:

SELECT * FROM
notifications
WHERE (receiverUserID = 3 AND status = 0 AND typ = 1) OR typ = 2;

This is due to that AND takes greater precedence than OR. This explains why all those typ = 2 records appear in your result set. You need to use parentheses to enforce the and/or logic you have in mind:

SELECT * FROM
notifications
WHERE receiverUserID = 3 AND status = 0 AND (typ = 1 OR typ = 2);

Note that had used WHERE IN (...) syntax this would have been a moot point:

SELECT * FROM
notifications
WHERE receiverUserID = 3 AND status = 0 AND typ IN (1, 2);

Upvotes: 1

Related Questions