How to join two sharepoint list in c# by CAML query?
I have created two list Markmaster and StudentMaster, both are connected with a lookup column(StudentID). I need to fetch data from both the list. List Detail are StudentMaster List Column are ID, Name, Class, SEction, RoleNumber MarksMaster List Column are ID, Title, ScienceGroup, ArtGroup, CommerceGroup, StudentID(Lookup)

Below is the code FYI, SPList customerList = Oweb.Lists["MarksMaster"]; query.Query = @"null";

        query.Joins = @"<Join Type='Left' ListAlias='StudentMaster'>
                            <FieldRef Name='StudentID' RefType='ID'/>
                            <FieldRef List='MarksMaster' Name='ID'/>

        query.ProjectedFields = @"<field Name='StudentName' Type='Lookup' List='MarksMaster' ShowField='Title'/>
                                <field Name='StudentSection' Type='Lookup' List='MarksMaster' ShowField='Section'/> ";

        query.ViewFields = @"<FieldRef Name='StudentSection'/>
                            <FieldRef Name='ScienceGroup'/>";

When am try to join the both list using above caml join query.

Value does not fall within the expected range.

3 Answers 3


Working with the CAML query is, for me, not so easy to do on a forum. So what I can suggest to you is to use a tool like that: http://spcamlqueryhelper.codeplex.com/

This will create a perfect CAML query for you, and you will be able to test it and then copy inside your c# or csom code.

  • Through it can we able to generate join caml query??
    – Praveen
    Commented Mar 25, 2014 at 2:12
  • 1
    @user24608 of course yes or why I should suggest to you something that will not help you? Commented Mar 25, 2014 at 5:47
  • 1
    This tool is actually kind of confusing, and I can't get joins to work nicely on it (SharePoint 2013 on-premise) Commented Mar 6, 2015 at 20:04

This does not directly answer your question but I have been able to retrieve stuff from a lookup list using this method:

/// <summary>
/// Returns the ID of a lookup column given the value of that lookup column
///  Example:
/// getLookUpValue(properties.ListItem["employeeIdNr"].ToString(), properties.List, "VPNaccess", "Corp_ID");
/// employeeIdNr contains the value of the lookup column
/// properties.List is the list that contains the lookup column
/// VPNaccess is the Lookup column
/// Corp_ID is the field from which the lookup column gets its values
/// </summary>
public static SPFieldLookupValue getLookUpValue(string lookupValue, SPList myList, string LookupField, string LookupSourceField, SPFieldCollection fields)
    if (fields.ContainsField(LookupField))
        SPFieldLookup lookUpField = (SPFieldLookup)myList.Fields[LookupField];
        SPList lookupSourceList = myList.ParentWeb.Lists.GetList(new Guid(lookUpField.LookupList), false); //ParentWeb.Lists[lookUpField.LookupList];

        SPQuery query = new Microsoft.SharePoint.SPQuery();
        query.Query = String.Format("<Where><Eq><FieldRef Name='{1}'/><Value Type='Text'>{0}</Value></Eq></Where>", lookupValue, LookupSourceField);
        SPListItemCollection listItems = lookupSourceList.GetItems(query);
        if (listItems.Count > 0)
            return new SPFieldLookupValue(listItems[0].ID.ToString());
    return null;

That gives me the ID of the item from the lookup list given the value of the lookup column, which I can use to pull the other corresponding data from the list. You might want to modify it slightly to return an int instead of a SPFieldLookupValue. Good luck!


Remember that you have to use list alias in Projected Fields Name. If you want to query MarksMaster list, then this will work (notice bold strings):

query.Joins = @"<Join Type='Left' ListAlias='StudentMaster'>
                      <FieldRef Name='StudentID' RefType='ID'/>
                      <FieldRef List='StudentMaster' Name='ID'/>

query.ProjectedFields = @"<field Name='StudentMasterName' Type='Lookup' List='StudentMaster' ShowField='Title'/>
                          <field Name='StudentMasterSection' Type='Lookup' List='StudentMaster' ShowField='Section'/> ";

query.ViewFields = @"<FieldRef Name='StudentMasterName'/><FieldRef Name='StudentMasterSection'/><FieldRef Name='ScienceGroup'/>";

//you will see fields from both lists in this table
DataTable dt = marksMasterList.GetItems(query).GetDataTable();

Please refer to these posts if u need good examples: http://blogs.msdn.com/b/kaevans/archive/2012/01/20/sharepoint-2010-caml-list-joins.aspx http://ranaictiu-technicalblog.blogspot.com/2011/01/u2u-caml-query-builder-for-sharepoint.html https://msdn.microsoft.com/en-us/library/ee539975(office.14).aspx

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.