CODEWITHSUNDEEP
mysql
Aravindhan
Aravindhan

Reputation: 3626

MySql OR and AND Operation

my table schema as follows:

                      +------+-------+-------+                                                                                                                                                         
                      | id   | name1 | name2 |                                                                                          
                      +------+-------+-------+                                                                                        
                      |    1 | A     | B     |
                      |    2 | A     | C     |
                      |    3 | B     | A     |
                      |    4 | C     | D     |
                      +------+-------+-------+

and i am using the query as follows:

          select * from t where name1='A' or name1='B' and name2='C';

and the result as follows

                       +------+-------+-------+
                       | id   | name1 | name2 |
                       +------+-------+-------+
                       |    1 | A     | B     |
                       |    2 | A     | C     |
                       +------+-------+-------+

How this result came and

I want the query which returns the row that in which the name1 will be A or B and name2 to be 'B'

the query result should be like this

                       +------+-------+-------+
                       | id   | name1 | name2 |
                       +------+-------+-------+
                       |    1 | A     | B     |
                       +------+-------+-------+

I want the result from the query using only "AND" and "OR" operation...

Upvotes: 0

Views: 1759

Answers (5)

paxdiablo
paxdiablo

Reputation: 882716

It's a precedence issue. The B and C clauses are binding together meaning you're getting:

select * from t where name1='A' or (name1='B' and name2='B');

So what you need to do is explicitly bind the clauses yourself:

select * from t where (name1='A' or name1='B') and name2='B';

(assuming your use of C in your stated query was a typo, since it didn't match the rest of your question).

Upvotes: 0

John Bustos
John Bustos

Reputation: 19574

   select * from t where (name1='A' or name1='B') and name2='B';

Upvotes: 0

Milhous
Milhous

Reputation: 14653

You should use parentheses 

select * from t where name1='A' or name1='B' and name2='C';

should be 

select * from t where (name1='A' or name1='B') and name2='B';

Upvotes: 0

John Woo
John Woo

Reputation: 263933

group your condition by adding parenthesis

select * from t where (name1='A' or name1='B') and name2='B';

OR use IN

select * from t where name1 IN ('A','B') and name2='B';

Upvotes: 0

Marc B
Marc B

Reputation: 360872

I suggest you read up about boolean logic. This is a very simple operation, so if you were familiar with B.L., you'd have been able to do this yourself.

That being said, these two states will accomplish what you want:

SELECT ... WHERE (name1 IN ('A', 'B')) AND (name2 = 'B')
SELECT ... WHERE ((name1 = 'A') or (name1 = 'B')) AND (name2 = 'B')

Upvotes: 2

Related Questions