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.


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.

Karsten Pohnke About Karsten Pohnke
He is Consultant for SharePoint Solutions for collaboration, communication and business processes. He provides his customers applications based on standard features as well as development or combining the power of several microsoft tools like Dynamics CRM. In his free time he tries to collect tipps and worthy experience in this blog.