SharePoint 2010 Join 2 Lists by using CAML

SharePoint 2010 Join 2 Lists by using CAML 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 = "";
query.Joins = "" +
"" +
"";
query.ProjectedFields = "";
query.ViewFields = "";

joinedResults = listFaculty.GetItems(query);

if (joinedResults != null && 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;