Not IN CAML Query

Question

I have two Lists in SharePoint 2007

1. Clients
2. Actions

Actions list has a Lookup column called Client which takes its values from Clients List. I want to display all those clients which are NOT present in Actions list. My approach is to first get all the clients from Clients list and then iteratively check them whether they are present in Actions list or not. This approach is not scalable and I was wondering if there is any better approach for it?

I am using GetListItems method of Lists.asmx web service as I do not have permission to deploy custom web part assembly on server.

Answer 1

See the <NotIncludes> CAML element here

Answer 2

I think the only option would be to build a CAML query with clauses in the .

So something like

<Query>
   <Where>
     <And>
       <Neq>
         <FieldRef Name="Title" /><Value Type="Text">Text</Value>
       </Neq>
       <Neq>
          <FieldRef Name="Title" /><Value Type="Text">Other Text</Value>
       </Neq>
     </And>
   </Where>
 </Query>

There is actually a LINQ to SharePoint project for SP 2007 on CodePlex. I've NEVER used it in production and it never got out of Alpha...

Answer 3

Don't think this is possibe in a single CAML query. Have you ever used U2U's CAML Query Editor ?

http://www.u2u.be/res/tools/camlquerybuilder.aspx

It's extremely useful for building CAML queries.

Shame your not using Sharepoint 2010 or you take advantage of some LINQ!

Ian.

Answer 4

should return the equivalent of

WHERE Title IN ([Title1], [Title2],...[TitleN]) 

for get list of title :

private string _camlTitleEq = "<Neq>" +
                             "<FieldRef Name=\"Title\" />" +
                             "<Value Type=\"Text\">{0}</Value>" +
                          "</Neq>";

private XElement BuildOrClause(List<string> listItemTitles, int index)
{
//If we've reached the last item in the list, return only an Eq clause
if (index == listItemTitles.Count - 1)
    return XElement.Parse(String.Format(_camlTitleEq, listItemTitles[index]));
else
{
    //If there are more items in the list, create a new nested Or, where
   BuildOrClause
    XElement andClause = new XElement("And");
    andClause.Add(XElement.Parse(String.Format(_camlTitleEq, listItemTitles[index])));
    andClause.Add(BuildOrClause(listItemTitles, index + 1));
    return andClause ;
}
}

and for set query

SPQuery query = new SPQuery();
string titleIn = BuildOrClause(listItemTitles, 0).ToString(SaveOptions.DisableFormatting);

query.Query = "<Where>" +
              titleIn +
          "</Where>";