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.
StudentMaster List Column are StudentID, Name, Class, SEction, RoleNumber
MarksMaster List Column are ID, Title, ScienceGroup, ArtGroup, CommerceGroup, StudentID(Lookup of Studentmaster List)
Below is the code FYI,
SPQuery query = new SPQuery();
SPList customerList = Oweb.Lists[“MarksMaster”];
query.Query = @”<Query><Where><Neq><FieldRef Name=’ID’ /><Value Type=’Counter’>null</Value></Neq></Where></Query>”;
query.Joins = “<Join Type=’Left’ ListAlias=’MarksMaster’>” +
“<Eq>” +
“<FieldRef Name=’StudentID’ RefType=’Id’/>” +
“<FieldRef List=’MarksMaster’ Name=’ID’/>” +
“</Eq>” +
“</Join>”;
query.ProjectedFields = “<field Name=’StudentID’ Type=’Lookup’ List=’MarksMaster’ ShowField=’StudentID’/>”;
query.ViewFields = “<FieldRef Name=’Section’/>” +
“<FieldRef Name=’ScienceGroup’/>”;
When am try to join the both list using above caml join query.
Stack Trace:
[ArgumentException: <nativehr>0x80070057</nativehr><nativestack></nativestack>]
MultiListQuery.MultiListQuery.MultiListQuery.Page_Load(Object sender, EventArgs e) +2112
System.Web.UI.Control.LoadRecursive() +95
System.Web.UI.Control.LoadRecursive() +190
System.Web.UI.Control.LoadRecursive() +190
System.Web.UI.Control.LoadRecursive() +190
System.Web.UI.Control.LoadRecursive() +190
System.Web.UI.Control.LoadRecursive() +190
System.Web.UI.Control.LoadRecursive() +190
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2936
Hi,
query.Query = @”<Query><Where><Neq><FieldRef Name=’ID’ /><Value Type=’Counter’>null</Value></Neq></Where></Query>“;
Remove <Query> tags from the CAML and try again. It should work. if not refer below links to know more about joining lists in SharePoint.
Hope this will help you.
