CAML Query with Lists in SharePoint 2013

Single Line of Text
<Where><Eq><FieldRef Name="Name" /><Value Type="Text">EmployeeName</Value></Eq></Where>
__________________________________________________________________

Multiple Lines of Text
<Where><Eq><FieldRef Name="Address" /><Value Type="Text">EmployeeAddress</Value></Eq></Where>
__________________________________________________________________

Choice
<Where><Contains><FieldRef Name='Province'/><Value Type='Choice'>East</Value></Contains></Where>
__________________________________________________________________

Number
<Where><Contains><FieldRef Name='Quantity'/><Value Type='Number'>0</Value></Contains></Where>
__________________________________________________________________

Date and Time
<Where><Eq><FieldRef Name="CurrentDate" /><Value Type="DateTime" IncludeTimeValue='FALSE'>2001-01-01</Value></Eq></Where>
__________________________________________________________________

Lookup By Lookup Name
<Query><Where><Eq><FieldRef Name="State" /><Value Type="Lookup">Arizona</Value></Eq></Where></Query>

Lookup By Lookup Id
<Query><Where><Eq><FieldRef Name="State" LookupId="TRUE" /><Value Type="Lookup">4</Value></Eq></Where></Query>
__________________________________________________________________

Yes/No
<Where><Eq><FieldRef Name="Active" /><Value Type="Boolean">1</Value></Eq></Where>
__________________________________________________________________

Person or Group By SPUser.Name
<Query><Where><Eq><FieldRef Name="Author" />Value Type="Text">Josh McCarty</Value></Eq></Where></Query>

Person or Group By SPUser.ID
<Query><Where><Eq><FieldRef Name="Author" LookupId="TRUE" /><Value Type="Integer"><UserID /></Value></Eq></Where></Query>
__________________________________________________________________

Hyperlink or Picture
<Where><Contains><FieldRef Name='URL'/><Value Type='URL'>http://facebook.com</Value></Contains></Where>
__________________________________________________________________

Calculated
<Where><Eq><FieldRef Name='Status' /><Value Type='Calculated'>Completed</Value></Eq></Where>
__________________________________________________________________

CAML Operators
<Eq> – Equals
<Neq> – Not Equals
<Gt> – Greater than
<Geq> – Greater than or Equals
<Lt> – Less Than
<Leq> – Less Than or Equals
<sNull> – Is Null (Is Empty)
<BeginsWith> – Begins with a string
<Contains> – Contains a string
__________________________________________________________________

ORDER BY
<OrderBy> <FieldRef Name="[Field_Name]" Ascending="True" /></OrderBy>

Ex: <Where><Eq><FieldRef Name="Name" /><Value Type="Text">EmployeeName</Value></Eq></Where><OrderBy> <FieldRef Name="Name" Ascending="True" /></OrderBy>

The default value of Ascending is TRUE (By Default Order is in Descending)

CAML Query with Person or Group Field in SharePoint

Querying By Display Name:-
Here is how to querying for Person or Group field by Display Name. Things that you have to concern here are Value Type is “User” and [User_Name] must be replaced with the Display Name (SPUser.Name) of the user.

Write the query as following:

<Where>
<Eq>
    <FieldRef Name='UserID' />
    <Value Type='User'>[User_Name]</Value>
</Eq>
</Where>

Querying By User ID:-
Advantage of querying by User ID is this will avoid failing of querying by Display Name when you have users who have same value for it. Here the most important thing to concern is that you have to add LookupId=”TRUE” property in FieldRef tag and also should replace [User_ID] with the ID (SPUser.ID) of the user.

Write the query as following:

<Where>
<Eq>
    <FieldRef Name='UserID' LookupId='TRUE'/>
    <Value Type='User'>[User_ID]</Value>
</Eq>
</Where>

Use Multiple Conditions in CAML Queries

1. One Condition

<Where>      
            <Eq><FieldRef Name=’Name’ /> <Value Type=’Text’>Aasai</Value></Eq>
</Where>

2. Two Conditions

<Where>
          <And>
            <Eq><FieldRef Name=’Title’ />  <Value Type=’Text’>Mr</Value></Eq>
            <Eq><FieldRef Name=’Name’ /> <Value Type=’Text’>Aasai</Value></Eq>
         </And>
</Where> 

3. Three Conditions

<Where>
      <And>
         <And>
            <Eq><FieldRef Name=’Title’ />  <Value Type=’Text’>Mr</Value></Eq>
            <Eq><FieldRef Name=’Name’ /> <Value Type=’Text’>Aasai</Value></Eq>
         </And>
         <Eq><FieldRef Name=’Address’ /> <Value Type=’Text’>Chennai</Value></Eq>
      </And>
   </Where>

4. Four Conditions

<Where>
      <And>
         <And>
            <Eq><FieldRef Name=’Title’ />  <Value Type=’Text’>Mr</Value></Eq>
            <Eq><FieldRef Name=’Name’ /> <Value Type=’Text’>Aasai</Value></Eq>
         </And>
        <And>
         <Eq><FieldRef Name=’Address’ /> <Value Type=’Text’>Chennai</Value></Eq>
         <Eq><FieldRef Name=’Country’ /> <Value Type=’Text’>India</Value></Eq>
         </And>
      </And>
   </Where>

5. Multiple Conditions

<Where>
    <And>
        <Or>
            <Eq>
                <FieldRef Name='FirstName' />
                <Value Type='Text'>John</Value>
            </Eq>
            <Or>
                <Eq>
                    <FieldRef Name='LastName' />
                    <Value Type='Text'>John</Value>
                </Eq>
                <Eq>
                    <FieldRef Name='Profile' />
                    <Value Type='Text'>John</Value>
                </Eq>
            </Or>
        </Or>
        <And>       
            <Or>
                <Eq>
                    <FieldRef Name='FirstName' />
                    <Value Type='Text'>Doe</Value>
                </Eq>
                <Or>
                    <Eq>
                        <FieldRef Name='LastName' />
                        <Value Type='Text'>Doe</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name='Profile' />
                        <Value Type='Text'>Doe</Value>
                    </Eq>
                </Or>
            </Or>
            <Or>
                <Eq>
                    <FieldRef Name='FirstName' />
                    <Value Type='Text'>123</Value>
                </Eq>
                <Or>
                    <Eq>
                        <FieldRef Name='LastName' />
                        <Value Type='Text'>123</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name='Profile' />
                        <Value Type='Text'>123</Value>
                    </Eq>
                </Or>
            </Or>
        </And>
    </And>
</Where>