How to get complex scenario results using CAML in Sharepoint

Question

I want to create Caml Query for following scenario,

(Metadata Name ="Author1" AND Value="Nitin Jagtap") AND (Metadata Name ="Program" AND Value="MBA Finance").

I have tried but not able to create CAML query for same. I have used Caml designer for same but unable to get the proper result.

Following is the Caml Query which is generated by using Caml designer but it is not giving results

<Where>
      <And>
         <And>
            <And>
               <Eq>
                  <FieldRef Name='Metadata_x0020_Name' />
                  <Value Type='Lookup'>Author 1</Value>
               </Eq>
               <Eq>
                  <FieldRef Name='Value' />
                  <Value Type='Text'>Nitin Jagtap</Value>
               </Eq>
            </And>
            <Eq>
               <FieldRef Name='Metadata_x0020_Name' />
               <Value Type='Lookup'>Program</Value>
            </Eq>
         </And>
         <Eq>
            <FieldRef Name='Value' />
            <Value Type='Text'> MBA Finance </Value>
         </Eq>
      </And>
   </Where>

I have a list having name “Metadata Values” which contains three columns,

1. Content Name(Lookup column from Content List)
2. Metadata Name(Lookup column from Metadata List)
3. Value(Single line of text ) Each contains has multiple metadata and values like below,

From above list I want to get Content Name which satisfy the condition

(Metadata Name ="Author1" AND Value="Nitin Jagtap") AND (Metadata Name ="Program" AND Value="MBA Finance").

Please guide me regarding above scenario.

Any help will be appreciable.

Thanks, Nitin

Answer 1

Below is different scenario of different type of lookup column.

Using LookupId:

 <FieldRef Name="Metadata" LookupId="True" />
 <Value Type="Integer">1</Value>

Using value as boolean:

  <FieldRef Name="Metadata" />
  <Value Type="Integer">1</Value>

Using value as date:

<FieldRef Name="Metadata" />
 <Value Type="DateTime">Your date in UTC</Value>

Using value as datetime:

     <FieldRef Name="Metadata" />
    <Value IncludeTimeValue="True" Type="DateTime">Your date in UTC</Value>

Using value as number:

<FieldRef Name="Metadata" />
    <Value Type="Number">1</Value>

Using value as Text:

    <FieldRef Name="Metadata" />
    <Value Type="Text">your text</Value>