SharePoint 2010 Join 2 Lists by using CAML

It’s maybe not new to you, but i found out, that you can join two or more lists with caml query. I tried the caml query builder but didn’t know how to use the join. So i created a small console app in order to try this out. I created two lists. List “Projects” with Title and a lookup column (“Phase”) to the second list “Phasen” to the title field. Now i would like to get a datatable which combines the values which are additional in the list “Phasen”, e.g.  the wow field.

The result should look like this afterwords:

The column “wow” comes from the second list “Phasen”. My Lists are set up like this:

Let me show you the code and afterword i explain it a little bit more.



SPListItemCollection joinedResults = null;
DataTable joinedResultsDataTable = null;

SPSite site = new SPSite("http://yourserver/sites/yoursitecollection/");
SPWeb web = site.OpenWeb();
SPList listFaculty = web.Lists["Projects"];

SPQuery query = new SPQuery();
//Here you can define your where clause
query.Query = "<where></where>";
query.Joins = "<join Type='Left' ListAlias='Phasen'>" +
"<eq><fieldref Name='Phase' RefType='ID' />" +
"<fieldref List='Phasen' Name='ID' /></eq></join>";
query.ProjectedFields = "<field Name='wow' Type='Lookup' List='Phasen' ShowField='wow'/>";
query.ViewFields = "<fieldref Name='Title' /><fieldref Name='Phase' /><fieldref Name='wow' />";

joinedResults = listFaculty.GetItems(query);

if (joinedResults != null &amp;amp;amp;amp;amp;&amp;amp;amp;amp;amp; joinedResults.Count > 0)
{
//Important - Don't know why, but otherwise it can't get the DataTable
int fieldCount = joinedResults.Fields.Count;
joinedResultsDataTable = joinedResults.GetDataTable();
}


As you can see, one of the important steps is to use the query.Join function. At first you define the list which you want to join, then you define the field in the main list which should make the reference, and then you define the field of the join list which is referenced. The reference goes to the id. It’s same like the lookup.

In the next step you define the projected fields. These are the field which are additional added to the results of the query. So they are like additional lookup fields, which you first define and then use in the viewfields.

Now you can put it into a datatable. But i had to use this code of line first:

int fieldCount = joinedResults.Fields.Count;

I don’t know why, but otherwise it throws errors. So just try it out. Hope this helps you.

..:: I LIKE SHAREPOINT ::..

The article or information provided here represents completely my own personal view & thought. It is recommended to test the content or scripts of the site in the lab, before making use in the production environment & use it completely at your own risk. The articles, scripts, suggestions or tricks published on the site are provided AS-IS with no warranties or guarantees and confers no rights.



About Karsten Schneider 308 Articles
Consultant for Microsoft 365 Applications with a strong focus in Teams, SharePoint Online, OneDrive for Business as well as PowerPlatform with PowerApps, Flow and PowerBI. I provide Workshops for Governance & Security in Office 365 and Development of Solutions in the area of Collaboration and Teamwork based on Microsoft 365 and Azure Cloud Solutions. In his free time he tries to collect tipps and worthy experience in this blog.