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.

01	SPListItemCollection joinedResults = null;

02	DataTable joinedResultsDataTable = null;

03

04	SPSite site = new SPSite("http://yourserver/sites/yoursitecollection/");

05	SPWeb web = site.OpenWeb();

06	SPList listFaculty = web.Lists["Projects"];

07

08	SPQuery query = new SPQuery();

09	//Here you can define your where clause

10	query.Query = "<where></where>";

11	query.Joins = "<join Type='Left' ListAlias='Phasen'>" +

12	"<eq><fieldref Name='Phase' RefType='ID' />" +

13	"<fieldref List='Phasen' Name='ID' /></eq></join>";

14	query.ProjectedFields = "<field Name='wow' Type='Lookup' List='Phasen' ShowField='wow'/>";

15	query.ViewFields = "<fieldref Name='Title' /><fieldref Name='Phase' /><fieldref Name='wow' />";

16

17	joinedResults = listFaculty.GetItems(query);

18

19	if (joinedResults != null && joinedResults.Count > 0)

20

{

21	//Important - Don't know why, but otherwise it can't get the DataTable

22	int fieldCount = joinedResults.Fields.Count;

23	joinedResultsDataTable = joinedResults.GetDataTable();

24

}

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;