Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

As per request by some of my readers who wanted information about how you can query SharePoint lists using joins in SharePoint 2010, here's an introduction!

With the introduction of relational lists in SharePoint 2010, they also introduced a new set of investments in how we can interact with our lists.

We now have Projected fields, joins and relational integrity between parent and child lists.

Projected Fields & Relational Integrity will be covered in an upcoming blog series.

Joins

With our new relational lists in SharePoint 2010, we also have a better support for joins. Generally there's the following ways of joining lists to pull out aggregated data:

1. SharePoint API (SPQuery object)
2. LINQ to SharePoint
3. CAML
4. SharePoint Designer 2010

In this article I will touch upon the SPQuery enhancements regarding joins and relational data, and will cover the rest in future articles.

SPQuery enhancements

Using the object model with SharePoint 2010, you now have the ability to use some new fancy properties of the SPQuery object:

1. SPQuery.Joins
2. SPQuery.ProjectedFields

SPQuery.Joins Property

With the new property called Joins on the SPQuery object, we can (using CAML, as usual..) define a join for our query like so:

<Joins>
    <Join Type='LEFT ListAlias='List1'>
        <Eq>
            <FieldRef Name='Field1' RefType='Id' />
            <FieldRef Name='ID' List='List1' />
        </Eq> 
    </Join> 

    <Join Type='LEFT' ListAlias='List2'>
        <Eq>
            <FieldRef List='List1' Name='SomeFieldName' RefType='Id' />
            <FieldRef List='List2' Name=’ID’ /> 
        </Eq>
    </Join>
</Joins>

A working sample of the SPQuery code could look like this:

SPQuery query = new SPQuery();
query.Query = "[YOUR CAML QUERY HERE]";
query.Joins = "[YOUR JOIN CAML HERE (See above for example)]";
query.ViewFields = "[SAME AS BEFORE]";

SPListItemCollection items = myList.GetItems(query);
foreach(SPListItem item in items)
    // Work with your items here like you're used to, but now all joined up!

So the only thing you really need to do is to make sure you've got the join set up with the SPQuery.Joins property - and then shoot!

SPQuery.ProjectedFields Property

The SDK states the ProjectedFields property quite clearly:

…itemizes fields from joined foreign lists so that they can be referenced in the Where element and the ViewFields element

You should have a go at this link to learn more about projected fields, and how you can utilize them in your SPQuery objects:
http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.projectedfields(office.14).aspx

Summary

It's pretty easy to do joins using the SPQuery object in the SharePoint object model these days, and all you really need to do is have a quick look at the SPQuery class in order to grasp the news.

So, pretty easy huh?
- Well, even more easy is to use LINQ to SharePoint, which I will try to cover in an article later this week.