How to Write An SPQuery Using Multiple AND OR Operators

Ok, if you’re like me, writing CAML queries can sometimes make your head hurt. Not because it’s terribly complicated, but because there’s not much useful information out there that demonstrates, with REAL examples, how these should be written. So let’s see if we can clarify this:

Scenario 1

Get me all items in a list WHERE fullName equals the currently logged in user.

1
2
3
4
5
6
7
8
9
10
11
SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
 
oQuery.Query = 
    "<Where>" + 
    "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" + 
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

Scenario 2

Get me all items in a list WHERE fullName equals the currently logged in user AND status equals ‘Complete’.

1
2
3
4
5
6
7
8
9
10
11
12
13
SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
        "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" + 
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

Scenario 3

Get me all items in a list WHERE fullName equals the currently logged in user AND status equals ‘Complete’ AND manager is James Lane.

This is where it gets a bit tricky. The following example is INCORRECT and will produce an error when run:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
        "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" +
        "<Eq><FieldRef Name='Manager'/><Value Type='Text'>James Lane</Value></Eq>" +
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

This is the correct way to do it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<And>" +
            "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
            "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" +
        "</And>" +
        "<Eq><FieldRef Name='Manager'/><Value Type='Text'>James Lane</Value></Eq>" +
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

Scenario 4

Get me all items WHERE fullName equals the currently logged in user AND status equals ‘Complete’ OR status equals ‘On Hold’.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SPWeb web = SPControl.GetContextWeb(Context);
 
string fullName = web.CurrentUser.Name;
 
SPQuery oQuery = new SPQuery();
oQuery.Query = 
    "<Where>" +
    "<And>" +
        "<Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + fullName + "'</Value></Eq>" +
        "<Or>" +
            "<Eq><FieldRef Name='Status'/><Value Type='Text'>Complete</Value></Eq>" +
            "<Eq><FieldRef Name='Status'/><Value Type='Text'>On Hold</Value></Eq>" +
        "</Or>" +
    "</And>" +
    "</Where>" +
    "<OrderBy><FieldRef Name='StartTime' Ascending='FALSE'></FieldRef></OrderBy>";

I really do recommend if you’re doing any work with CAML queries that you download the U2U CAML Query Builder 2007. It doesn’t come without it’s issues, for example, doing a query on a list with a column of type Lookup does not always yield the results I would expect, but it’s still quite helpful. I also often find myself changing the Value Type from Lookup to Text but other than that it’s a huge resource when trying to determine if the data is actually in the list or if there is a problem with the query i’ve written.

Any feedback or questions, please drop me a line.

,

5 Responses to “How to Write An SPQuery Using Multiple AND OR Operators”

  1. Dilip Nilanka September 18, 2011 at 5:43 am #

    Great Post!

  2. Hazama November 4, 2011 at 1:10 am #

    I am developing a custom search screen. I want to get a list of items based on their status. For example: Approved OR Unapproved OR Rejected. I know that the CAML query should be something like ApprovedUnapproved/Rejected. How do I dynamically generate the CAML query for different searches?

  3. shereen November 18, 2011 at 3:53 pm #

    Hi Hazama,

    What is the name of your Status column and what type of column is it? Depending on what it is, you could write something like:

    <Eq><FieldRef Name='FullName'/><Value Type='Text'>'" + searchValue + "'</Value></Eq>

    In the above query, the searchValue is what you would read from your search input. Does that answer your question?

  4. Mehboob Ali August 23, 2013 at 1:45 pm #

    Nice article!!!

Trackbacks/Pingbacks

  1. Links (4/30/2009) « Steve Pietrek - Everything SharePoint and Office - April 30, 2009

    […] How to Write An SPQuery Using Multiple AND OR Operators […]

Leave a Reply