CAML “NOT IN” query

camlsharepoint

Is there a way to do something like "NOT IN" behavior in SharePoint 2010? I can easily implement IN behavior like that:

<Where>
   <In>
      <FieldRef Name="ID"/>
      <Values>
         <Value Type="Counter">1</Value>
         <Value Type="Counter">2</Value>
         <Value Type="Counter">3</Value>
         <Value Type="Counter">4</Value>
         <Value Type="Counter">5</Value>
      </Values>
   </In>
</Where>

But is there a way to select all the values that DOES NOT IN Values enumeration?

Here is the USE CASE: I have a Lookup field with AllowMultipleValues = true, and I need to get all items from LookupList, which are not included into Lookup field

Thanks in advance!

Best Answer

Starting from SharePoint 2010, there's the NotIncludes element that might work for you. From MSDN:

If the specified field is a Lookup field that allows multiple values, specifies that the Value element is excluded from the list item for the field that is specified by the FieldRef element.

Template:

<NotIncludes>
    <FieldRef Name="Field_Name" />
    <Value Type="Field_Type" />
    <XML />
</NotIncludes>
Related Topic