Caml Query to fetch the items where the Lookup Column value is not null

sharepoint

I have a list of employees in sharepoint. One of the columns in the Employee list is a lookup Column called Organization.
I am trying to write a CAML query to fetch all the employees where the Organization value is not empty. But, I receive all the items even if the value of Organization is empty.
This is what I am trying

           string querystring = string.empty;
           querystring = "<Where><IsNotNull><Field RefName='EmployeeOrganization_x0020_Organization' /></IsNotNull></Where>";

This query is returning all the items in the list.

SPQuery query = new SPQuery();
query.Query = "<Where><IsNotNull><FieldRef Name='EmployeeOrganization_x0020_Organization'/></IsNotNull></Where>";
DataTable dtemp = emplist.GetItems(query).GetDataTable();
  if (dtemp != null)
            {
               GridView1.DataSource = dtemp ;
                GridView1.DataBind();
            }

Best Answer

<Query>
  <Where>
    <IsNotNull>
       <FieldRef Name='Project'/>
    </IsNotNull>
  </Where>
</Query>

Is working for me.You should use U2U CAML Query Builder to build the Query by using the Designer.. It automatically generates the required CAML expression for you.

Can you provide more from your context. What are your settings on the SPQuery instance?

Edit:

<Field RefName='Project'/>

to

<FieldRef Name='Project'/>

Related Topic