MySql query on combination of AND and OR

Question

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.

Teja · Accepted Answer · 2012-03-09 19:56:28Z

9

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

answered Mar 9, 2012 at 19:56

Teja

13.7k38 gold badges103 silver badges164 bronze badges

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

2 Comments

Prasad Over a year ago

I need atleast 15 reputation to do that. But anyway, I have posted it as useful one. Thanks a lot. I didn't know that one could place brackets there like that. :)

showdev Over a year ago

An explanation would be helpful to future readers. What did you change and how does it solve the problem?

Michael Fredrickson · Accepted Answer · 2012-03-10 20:33:01Z

3

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

answered Mar 10, 2012 at 20:33

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Comments

kasavbere · Accepted Answer · 2012-03-09 20:12:57Z

2

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%'

answered Mar 9, 2012 at 20:12

kasavbere

6,02314 gold badges52 silver badges73 bronze badges

1 Comment

Prasad Over a year ago

Hi, your code is working fine. But the problem is I have to give it as '%maths%'. If I give it as 'maths', it gives me wrong answer. Atleast, I have to keep one % symbol on either side to make the code work. But what if, I had something like 'Maths A','Maths' and 'A Maths' and I wanted only 'Maths' to come up, how do I work around in that situation? Also, in concat_ws, what is the first parameter where you have left one empty ' '? I liked your solution but.

Collectives™ on Stack Overflow

MySql query on combination of AND and OR

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related