Skip to main content
3 of 4
edited title

Need CAML query LookupMulti column help, Is it possible?

I am trying to create a CAML query for querying list, here is my problem. There is a list Employees and it has a LookupMulti column for Hobbies. I want to get all Employees who have selected Reading as their secondary Hobby, means the Employee should have more than one Hobbies selected and one of them should be Reading.

Please check following code for what I have tried so far

        SPList list = web.Lists[Constants.List_Employees];

            SPQuery query1 = new SPQuery();

            //Create caml query
            var queryBuilder = new StringBuilder("<View><ViewFields>");

            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_ID);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_EmployeeStatus);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_InsuranceCompanyName);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_Title);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_ABSEmployeeRegion);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_ABSGender);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_ABS1ParentCaregiverEmail);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_ABS2ParentCaregiverEmail);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_MedicaidID);
            queryBuilder.AppendLine("</ViewFields>");

            queryBuilder.AppendLine("<Where>");
            queryBuilder.AppendLine("<And>");

            queryBuilder.AppendLine("<Eq>");
            queryBuilder.AppendFormat("<FieldRef Name='{0}'></FieldRef>", Constants.Employee_Field_EmployeeStatus);
            queryBuilder.AppendFormat("<Value Type='Choice'>{0}</Value>", Constants.Employee_Status_Active);
            queryBuilder.AppendLine("<Eq>");

            //This is where I am struggling to write CAML query
            queryBuilder.AppendLine("<Contains>");
            queryBuilder.AppendFormat("<FieldRef Name='{0}'></FieldRef>", Constants.Employee_Field_Hobbies);
            queryBuilder.AppendFormat("<Value Type='Choice'>{0}</Value>", Constants.Employee_Hobby_Reading);
            queryBuilder.AppendLine("<Contains>");

            queryBuilder.AppendLine("</And>");
            queryBuilder.AppendLine("</Where>");
            queryBuilder.Append("</Query></View>");

            query1.ViewXml = queryBuilder.ToString();

            return list.GetItems(query1);

Please help. Thanks.