Using AND OR in a Query List

[davidjohnson500]

I am really confused on the AND OR filters in a query list.  As an example I want to filter a list if:

The student must be a Senior (her grade) AND must be female (her gender)

There sports preference can be soccer OR swimming OR rugby

I tried doing the below in the Query List but it essentially retuned the whole list; it did not filter.

Grade = Senior

AND

Gender = Female

OR

Sports

= Basketball

OR

Sports

=soccer

OR

Sports

=swimming

[rhia]

So right away looking at this, I can see that it's probably reading it as "must be senior AND (female OR basketball OR soccer OR basketball OR swimming)"

I think it's reading everything after your first "AND" as part of the collection of ORs.

Perhaps it needs to be senior AND female AND (basketball OR swimming OR soccer).

I think that would work, but I am not 100%. I will see if I get a chance to try it out tomorrow.

[emha]

conditions are evaluated from top to bottom.

so first GRADE and GENDER are ANDed and result is ORed with Basketball then ORed with soccer etc.

bit illogical approach and totally not maintainable, but there is not other way with clickable 'Query builder'

I would suggest to switch to 'CAML editor' and write your CAML query completely on your own. this gives you opportunity to group/prioritize single conditions as logically needed and it as well much more easy to maintain.

[murphybp2 · Accepted solution]

If you change the order of your query conditions, you can get it to work.  As said, the query reads from top to bottom, and add each criteria to the previous.  So the AND criteria should be last, and the OR criteria first.  

I created the mock table below as a list. The records in red are the results I want to return based on your criteria. 

Title	Gender	Grade	Sports
Joe Smith	Male	Freshman	Basketball
Jim Jordan	Male	Senior	Soccer
Julie Smith	Female	Freshman	Soccer
Tina Tombs	Female	Senior	Basketball
Jane Doe	Female	Senior	Hockey
Sally Swift	Female	Senior	Swimming
Susie Que	Female	Junior	Basketball
Sam Smart	Female	Senior	Soccer

So I set up my Query List criteria as follows, with the 3 sports criteria first, and then the Gender and Grade last.  You can use the Run Now option to see the results, as well as the way it structured the CAML query. 

In this case it structured it as follows.  You can see that the outer condition is an AND, and the inner Sports criteria are wrapped in an OR.  So it will find everything that is Senior AND Female AND Sports is Basketball OR Soccer OR Swimming.  So it returns the records in red from the table above. 

With the way you currently have it structured, the CAML looks like this, with the outer condition being an OR condition.  So this query is looking for everything that where Sports is Swimming OR Soccer OR Basketball OR the Grade is Senior AND Gender is Female.   

So if you had a Male Swimmer, this Query would return that record.  

Hope that helps. 

[davidjohnson500]

Perfect.  Your explanation and example make total sense.  Thanks Marian and Rhia for the assist.

[murphybp2]

Great. And please don't forget to mark the question as answered.

[rhia]

I did not know this! Thank you for sharing!