CAML query using OR and AND Condition

Question

I need to write a CAML query which satisfies the below condition:

projectstatus == "hold" OR projectstatus == "obsolete "
AND ilprilt not equal to "ilp frozen"

When I run this query, am getting records where

ilprilt not equal to "ilp frozen" and ilprilt not equal to "ilp not frozen"

I am missing something. Please help with the correct query. I believe the placement of AND / OR operator is giving me the error

projectstatus -- list column

ilprilt -- list column

projectstatus values -- hold / obsolete

ilprilt values --- > ilp not frozen /ilp frozen

Below is my query which isn't giving me correct results:

<Query>
    <Where>
        <Or>
            <Eq>
                <FieldRef Name='ProjectStatus' />
                <Value Type='Choice'>Hold</Value>
            </Eq>
            <And>
                <Eq>
                    <FieldRef Name='ProjectStatus' />
                    <Value Type='Choice'>Obsolete</Value>
                </Eq>
                <Neq>
                    <FieldRef Name='ILPRILT' />
                    <Value Type='Choice'>ILP Frozen</Value>
                </Neq>
            </And>
        </Or>
    </Where>
</Query>

Answer 1

Placement of AND and OR in combine query always confuses us.

The only mistake I see in your query is, you have miss placed the AND and OR elements.

The correct query will be:

<Query>
   <Where>
      <And>
         <Or>
            <Eq>
               <FieldRef Name='projectstatus' />
               <Value Type='Choice'>hold</Value>
            </Eq>
            <Eq>
               <FieldRef Name='projectstatus' />
               <Value Type='Choice'>obsolete</Value>
            </Eq>
         </Or>
         <Neq>
            <FieldRef Name='ilprilt' />
            <Value Type='Choice'>ilp frozen</Value>
         </Neq>
      </And>
   </Where>
</Query>

This will give you what you want.

You can also go through How to build nested query in CAML query to get more idea regarding how to build the nested query with AND and OR.

Answer 2

Try this - I think this will do it. I have not tested it.

<Query>
<Where>
<And>
<Or>
<Eq> <FieldRef Name='ProjectStatus' /> <Value Type='Choice'>Hold</Value> </Eq>
<Eq> <FieldRef Name='ProjectStatus' /> <Value Type='Choice'>Obsolete</Value> </Eq>
</Or>
<Neq> <FieldRef Name='ILPRILT' /> <Value Type='Choice'>ILP Frozen</Value> </Neq>
</And>
</Where>
</Query>

I find utilities like SPCamlHelper to be useful for testing these queries.

https://spcamlqueryhelper.codeplex.com