I am trying to create the Caml query for the following Scenario:

I have two Lists:

  1. DocumentApprovalRequest
  2. DocumentApprovalComments

Both the Lists has the column "DocumentNumber", I want to join both the lists on the basis of this Column and get the all the matched Data from 1st list only.

The second list has the column "CommentBy" , here I want to Pass the userID in where clause then the Requests where the comment of the passed User ID exists should be returned.

I've tried the following query but it's throwing Exceptions:

 public DataTable GetProcessedRequests(string UserID)
            using (SPSite site = new SPSite(SPContext.Current.Web.Url))
                using (SPWeb web = site.OpenWeb())
                    SPList list = web.Lists.TryGetList("DocumentApprovalRequest");
                    var q = new SPQuery()

                        Query = @" <OrderBy> <FieldRef Name='Created' Ascending='False' /> </OrderBy>"

                    q.Joins = @"<Join Type='Inner' ListAlias='DocumentApprovalComments'>
                      <FieldRef Name='DocumentNumber' RefType='ID'/>
                      <FieldRef List='DocumentApprovalComments' Name='ID'/>

                    q.ProjectedFields = @"<Field Name='DocumentNumber'  List='DocumentApprovalComments' ShowField='Title'/>
                                    <Field Name='DocumentNumber'  List='DocumentApprovalRequest' ShowField='Title'/> ";

                    q.ViewFields = @"<ViewFields>
                          <FieldRef Name='RequestStatus'/>
                    q.ViewFields = @"<ViewFields>
                          <FieldRef Name='RequestBy'/>
                    q.ViewFields = @"<ViewFields>
                          <FieldRef Name='DocumentNumber'/>

                    var r = list.GetItems(q);

                    DataTable dtResults = r.GetDataTable();

                    return dtResults;

Please help, How can I pass the user id for "CommentBy" column of the second list in the where clause


1 Answer 1


"There are requirements to keep in mind when creating list joins. You cannot join just any two lists, regardless of type. And if two lists can be joined, not just any primary and foreign field can be used as the "join on" pair of fields. The field in the primary list must be a Lookup type field and it must lookup to the field in the foreign list. For this reason, all joins mirror existing lookup relations between lists."

Previous tested caml(CSOM/JSOM, while the query should be same) from the thread.

var CAMLJoins =
               "<Joins>" +
               "<Join Type='INNER' ListAlias='Person'>" +
               "<Eq>" +
               "<FieldRef Name='Person' RefType='Id'/>" +
               "<FieldRef List='Person' Name='ID'/>" +
               "</Eq>" +

        var CAMLProjFlds =
            "<ProjectedFields>" +
            "<Field Name='DLastName' Type='Lookup' List='Person' ShowField='LastName'/>" +
            "<Field Name='DFirstName' Type='Lookup' List='Person' ShowField='FirstName'/>" +

        var ViewXml = "<View><Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>A</Value></Eq></Where></Query><ViewFields><FieldRef Name='Title'/><FieldRef Name='DLastName'/><FieldRef Name='DFirstName'/></ViewFields>" + CAMLProjFlds + CAMLJoins + "<RowLimit>100</RowLimit></View>";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.