get mysql data where column has 2 values

Question

I have the following mysql query:

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

the result:

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 ?

WHERE `receiverUserID` = 3 AND `status` = 0 AND (`typ` = 1 OR `typ` = 2) — Nick
– Nick, Commented May 30, 2020 at 8:11
Note that the row with receiverUserID = 3 should also not be shown since it has status = 1 — Nick
– Nick, Commented May 30, 2020 at 8:15

CodeTalker · Accepted Answer · 2020-05-30 08:22:24Z

3

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).

edited May 30, 2020 at 8:22

answered May 30, 2020 at 8:13

CodeTalker

1,8314 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jSebestyén Over a year ago

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

CodeTalker Over a year ago

@jBuchholz Thanks a lot for adding a much valuable thing. Appreciate it. If you allow, may I add it in my answer?

Tim Biegeleisen · Accepted Answer · 2020-05-30 08:13:15Z

1

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);

answered May 30, 2020 at 8:13

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

get mysql data where column has 2 values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related