Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer
    
Through it can we able to generate join caml query?? –  Praveen Mar 25 at 2:12
    
@user24608 of course yes or why I should suggest to you something that will not help you? –  Salvatore Di Fazio Mar 25 at 5:47

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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