CAML Query Where (x AND y) OR Z

Question

I've been struggling with this all day. I've referenced several SO questions/answers but to no avail. Not quite sure what I'm doing wrong. I also cannot use a CAML Builder, unfortunately, due to some security restrictions.

Here is my custom list, users:

LastName | FirstName | NameID

I'm trying to write my CAML query to say, WHERE (LastName = X AND FirstName = Y) OR NameID = Z

I can do the first part just fine, grabbing where LastName = X and FirstName = Y.

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

But as soon as I try to say that statement OR where NameID = Z, I run into issues. I've tried several queries, my latest being:

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

Can anyone point me in the right direction?

Answer 1

<Or> should be the outer one and <And> should be inside.

Try below:

    <Where>
      <Or>
                <Eq><FieldRef Name='Name' /><Value Type='Text'>JDoe123</Value></Eq>

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

        </And>
</Or>
    </Where>

Answer 2

While writing CAML Query you should keep few points in mind.

• Every <Or> / <And> can consist of only 2 conditions

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

• If so try formatting it so that above <And> becomes 1 conditional block.

Now you can pair it with another condition

 <Or>
        <Eq>
            <FieldRef Name='Name' /><Value Type='Text'>JDoe123</Value>
        </Eq>
        <Another conditional block here>
 </Or>

Here you can replace <Another conditional block here> with your previously created conditional block of <and> so it will again be treated as a single condition

So In all your query will look like :

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