CODEWITHSUNDEEP
mysql
Prasad
Prasad

Reputation: 6034

MySql query on combination of AND and OR

I want to write a MySql query whereby I want to use combination of AND and OR. I have 6 attributes where in I want one String to appear AND there is one attribute in which a compulsory thread should appear.

My command looks like this.

Select * from Students where roll_no like '__2011%' and subject1 ='maths' or subject2='maths' or subject3='maths' or subject4='maths' or subject5='maths' or subject6='maths';

I want 'maths' to appear in atleast one of those subject attributes. Along with that Roll no should have 2011 in it as specified in the query. Please help me.

Upvotes: 3

Views: 5133

Answers (3)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

When you have multiple or conditions, in is a cleaner way to handle this:

Select * 
from Students 
where 
    roll_no like '__2011%' and 
    'maths' in (subject1, subject2, subject3, subject4, subject5, subject6);

Upvotes: 3

kasavbere
kasavbere

Reputation: 6003

This may be a bit faster than multiple "or"

select * from students where roll_no like '__2011%' and
 concat_ws(' ',subject1,subject2,subject3,subject4,subject5,subject6) like '%maths%'

Upvotes: 2

Teja
Teja

Reputation: 13544

Select * from Students where roll_no like '__2011%' and (subject1 ='maths' or subject2='maths' or subject3='maths' or subject4='maths' or subject5='maths' or subject6='maths');

Upvotes: 8

Related Questions