Help with Complex CAML Query - Multiple OR Conditions

Question

I need some assistance with a complex CAML query. I can get parts of it working but cannot get all of it working together. What I need in laymans terms is this:

Where Country = US 

AND Status BEGINSWITH A OR Status BEGINSWITH B OR Status BEGINSWITH C 
OR Status BEGINSWITH D OR Status BEGINSWITH E OR Status BEGINSWITH F

AND Relationship NOT EQUALS to N/A OR IsComplete EQUALS Yes OR IsApproved EQUALS Yes

I have the statement laid out like this:

<Where>
<And>
        <Eq><FieldRef Name='ContractingCountry' /><Value Type='Text'>Canada</Value></Eq>
        <Or>
            <Or>
              <Or>
              <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>A</Value></BeginsWith>
              <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>B</Value></BeginsWith>
             </Or>
              <Or>
                  <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>C</Value></BeginsWith>
                  <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>D</Value></BeginsWith>
              </Or>
            </Or>
            <Or>
                <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>E</Value></BeginsWith>
                <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>F</Value></BeginsWith>
            </Or>
        </Or>

</And>
<And>
        <Or>
              <Neq><FieldRef Name='Relationship' /><Value Type='Text'>N/A</Value></Neq>
            <Or>
                <Eq><FieldRef Name='IsComplete' /><Value Type='Text'>Yes</Value></Eq>
                <Eq><FieldRef Name='Approved' /><Value Type='Text'>Yes</Value></Eq>
            </Or>
        </Or>
</And>
</Where>

The Query runs but it seems to ignore the last part of the statement as it is pulling back things where the Relationship = N/A.

Answer 1

According to your condition following should be the CAML Query.
Condition

(Country == US) && (Status BeginsWith A || Status BeginsWith B || Status BeginsWith C || Status BeginsWith D || Status BeginsWith E || Status BeginsWith F) && (Relationship != NA || IsComplete == Yes || IsApproved == Yes)

CAML Query

<Where>
    <And>
        <And>
            <Eq><FieldRef Name='Country'/><Value Type='Text'>US</Value></Eq>
            <Or>
                <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>A</Value></BeginsWith>
                <OR>
                    <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>B</Value></BeginsWith>
                    <OR>
                        <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>C</Value></BeginsWith>
                        <OR>
                            <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>D</Value></BeginsWith>
                            <Or>
                                <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>E</Value></BeginsWith>
                                <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>F</Value></BeginsWith>
                            </Or>
                        </Or>
                    </Or>
                </Or>
            </Or>
        </And>
        <Or>
            <Neq><FieldRef Name='Relationship'/><Value Type='Text'>N/A</Value></Neq>
            <Or>
                <Eq><FieldRef Name='IsComplete'/><Value Type='Text'>Yes</Value></Eq>
                <Eq><FieldRef Name='IsApproved'/><Value Type='Text'>Yes</Value></Eq>
            </Or>
        </Or>
    </And>
</Where>

Please check if it works for you.

Answer 2

You must first understand how it works and how to write.Then you can do it on your own.

How To Write Dynamic CAML :

While writing CAML query, you must understand the architecture how it works.

It is pretty simple. You just have to do logical AND/OR operators.

Any operator must have 2 conditions to combine and test.

SCENARIO 1 : This is 1 Operator use with 2 conditions

 <Or>
      <Eq><FieldRef Name='Status' /><Value Type='Text'>(3) TestStatus3</Value></Eq>
       <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>              
 </Or>

SCENARIO 2 : This is 2 Operators use with 2 conditions From scenario 1 we got operator now along with that we will add another operator.

<And>
    <Or>
      <Eq><FieldRef Name='Status' /><Value Type='Text'>(3) TestStatus3</Value></Eq>
       <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>              
    </Or>
    <Eq><FieldRef Name='Status' /><Value Type='Text'>(1) TestStatus1</Value></Eq>                  
</And>

Here if you notice, <OR> is having two conditions inside it. and that makes 1 condition for outer <And>, so we added another condition block parallel to <OR> operator. Hence 2 conditions are available for <AND>.

Likewise you can build n number of nested conditions.

PS: If you have only 1 condition, it will not require any operator.