How to join two sharepoint list in c# by CAML query?

Question

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'>
                            <Eq>
                            <FieldRef Name='StudentID' RefType='ID'/>
                            <FieldRef List='MarksMaster' Name='ID'/>
                        </Eq>
                        </Join>";

        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.

Answer 1

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.

Answer 2

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!

Answer 3

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'>
                   <Eq>
                      <FieldRef Name='StudentID' RefType='ID'/>
                      <FieldRef List='StudentMaster' Name='ID'/>
                   </Eq>
                </Join>";

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