SP 2010: List Joins & SPQuery enchancements!

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.

Follow me

Tobias Zimmergren

Founder / Consultant / Advisor at TOZIT AB
Tobias Zimmergren delivers high-quality articles about business and technology around the Microsoft scene.

Tobias focuses on advisory and consultancy for the Office 365 and SharePoint offerings from Microsoft.
Follow me
  • Harish 08

    Hello

    I have done the same way as explained here but I could not able to acheive it here..
    I am getting error
    0x80070057

    • Snehal Parkar

      Hi Harish,
      I also got the same error, this is because caml joins works on the child list like for e.g. Candidates (candidatename, age, address) and CandidateJobDetails(Manager, jobTitle), candidatejobdetails(i.e. child list) contain the lookup to Candidate(i.e. parent list) with some column called candidatename. Hence you need to query on CandidateJobDetails.GetItems(query) and you can get the Projected Fields information from Parent candidate.
      There is no way i feel i can query parent and get Projected field information from child on which i always get the error which you are facing

      • snehal parkar

        Yes but it is possible through linq but i again do not get any proper caml query which linq is utilising to achieve the same internally.

  • Arpan Kar

    Disgusting… Where the MyList is comming from. Write blog with proper example. Don’t confuse ppl.

    • http://www.zimmergren.net/ Tobias Zimmergren

      Hi Arpan. Thank you for that comment. I’m sorry you think my blog post is disgusting, but each to his own.

      This was written a long time ago when all of this were still pretty fresh. There’s plenty of places around the web with more elaborate samples. Go a round on Google and you’ll find this one for example: http://rmanimaran.wordpress.com/2011/01/27/sharepoint-2010-using-spquery-joins-and-projectedfields/

      Enjoy :-)
      Tobias.

    • TheSharePointinator

      This is a disgusting comment!

  • Carl

    I agree with Arpan, can you expand your example and explain where the myList is coming from?

    • http://www.zimmergren.net/ Tobias Zimmergren

      Hi Carl,
      Thank you for the comment.
      Check out what I replied to Arpan above and you’ll find a more elaborate sample there. This post was written a long time ago :-)
      Cheers,
      Tob.

  • Ángel

    How could you retrieve the values of a field like “Author” of one of the related lists (not the main list)?

    • http://www.zimmergren.net/ Tobias Zimmergren

      CAML Query or LINQ. I’d prefer CAML though.
      I normally build wrappers for everything so I easily can call a common core function which then does the magic for me.

      If you need a more specific answer to a specific problem, please visit http://www.mssharepointforums.com :-)

  • Chris

    Hi

    I’m trying to implement Joins in View element in XML view definition.

    I created Joins, ProjectedFields and ViewFields elements outside Query element (like in this MSDN article: http://msdn.microsoft.com/en-us/library/office/ee539975(v=office.14).aspx).

    Unfortunately I get InvalidCastException: Unable to cast object of type ‘System.Xml.XmlElement’ to type ‘System.String’

    Do you have a Joins example inside view definition?

    • http://cibpoint.com Сергей Снитко

      I Found the solution. I had the same error in 2013, and didn’t get it in 2010. In 2013 different render of grids. It uses JSGrid and the error apears when it tryes to render the projected field. It tryes to get field WebId and if it is empty, the error apears.

      So if you fill WebId, the error will disaper.
      This block helped me:

      Now I’am trying to solve this problem somehow more globaly.