Display data by joining 2 lists using CAML : SharePoint 2010

One of the cool new features in SharePoint 2010 is to be able to perform joins using CAML query between 2 or more lists.
This example demonstrates how we can perform join between 2 lists on the basis of lookup field.

In this example, we have 2 lists named 'Courses' and 'Faculty'.

The 'Courses' list contains all courses being taught to students along with the 'Lecturer' taking the courses.The 'Lecturer' field is a lookup to the 'Title' field in 'Faculty' list which contains the name of the lecturer.This is the field on which the join will be performed.

It also contains a 'TargetGroup' field specifying which audience this post is targetted to like developer, administrator,designer etc. as shown below:

The 'Faculty' list is the master list containing all the details of the lecturers like their names and email addresses.

Now, we want to get the course title,course lecturer, lecturer's email and course's target group from the 'Courses' list where the 'TargetGroup' is 'developer'.

This can be done by creating an application page having a gridview which will display the results of the query.
The method 'GetJoinedQueryResults()' gets the records by joining 2 lists 'Courses' and 'Faculty' using the 'Lecturer' lookup field.

The query.Query contains the where clause for the query,
The query.Joins field specifies the join statement on the 'Lecturer' field using list alias 'courseLecturers' for the list
'Faculty'.
The query.ProjectedFields specifies the fields to get from the list being joined ; like 'Email' from the list 'Faculty'.
The query.ViewFields gets the fields to be displayed in the result.

The complete code for the application page along with the codebehind is given below:

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

<%@ Import Namespace="Microsoft.SharePoint.ApplicationPages" %>

<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>

<%@ Import Namespace="Microsoft.SharePoint" %>

<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DisplayItemsFromJoinedCAMLQuery.aspx.cs" Inherits="Blog_Test_Project.Layouts.Blog_Test_Project.DisplayItemsFromJoinedCAMLQuery" DynamicMasterPageFile="~masterurl/default.master" %>

<asp:Content ID="PageHead" ContentPlaceHolderID="PlaceHolderAdditionalPageHead" runat="server">

</asp:Content>

<asp:Content ID="Main" ContentPlaceHolderID="PlaceHolderMain" runat="server">

<asp:Label ID="lblError" runat="server" Visible="false"></asp:Label>

<asp:GridView ID="gvJoinedQueryResults" runat="server" AutoGenerateColumns="false" Width="100%">

                           <EmptyDataRowStyle />

                            <EmptyDataTemplate>

                                No records found.

                            </EmptyDataTemplate>

                           <Columns>

                                <asp:BoundField DataField="Title" HeaderText="Course Title" />

                                <asp:BoundField DataField="Lecturer" HeaderText="Lecturer" />

                                <asp:BoundField DataField="Email" HeaderText="Lecturer Email" /> 

                                <asp:BoundField DataField="TargetGroup" HeaderText="Target Group" />                                                                                           

                            </Columns>

                         

                        </asp:GridView>

</asp:Content>

<asp:Content ID="PageTitle" ContentPlaceHolderID="PlaceHolderPageTitle" runat="server">

View Results of a joined query

</asp:Content>

<asp:Content ID="PageTitleInTitleArea" ContentPlaceHolderID="PlaceHolderPageTitleInTitleArea" runat="server" >

View Results of a joined query

</asp:Content>

namespace Blog_Test_Project.Layouts.Blog_Test_Project

{

public partial class DisplayItemsFromJoinedCAMLQuery : LayoutsPageBase

    {

        protected void Page_Load(object sender, EventArgs e)

        {

           

                BindData();

           

        }

        public void BindData()

        {

            try

            {

                DataTable joinedQueryResults = GetJoinedQueryResults();

                if (joinedQueryResults != null)

                {

                    gvJoinedQueryResults.DataSource = joinedQueryResults;

                    gvJoinedQueryResults.DataBind();

                }

                else

                {

                    lblError.Text = "No records found.";

                    lblError.Visible = true;

                 

                }

               

            }

            catch (Exception ex)

            {

                lblError.Text = "An error occurred:" + ex.ToString();

                lblError.Visible = true;

                gvJoinedQueryResults.Visible = false;

            }

        }

        private DataTable GetJoinedQueryResults()

        {

            SPListItemCollection joinedResults = null;

            DataTable joinedResultsDataTable = null;

            SPWeb site = SPContext.Current.Web;

            SPList listFaculty = site.Lists["Faculty"];

            SPList listCourses = site.Lists["Courses"];

            SPQuery query = new SPQuery();

            query.Query = "<Where><Eq><FieldRef Name=\"TargetGroup\"/>" +

            "<Value Type=\"Text\">Developer</Value></Eq></Where>";

            query.Joins = "<Join Type=\"Inner\" ListAlias=\"courseLecturers\">" +

            "<Eq><FieldRef Name=\"Lecturer\" RefType=\"Id\" />" +

            "<FieldRef List=\"courseLecturers\" Name=\"Id\" /></Eq></Join>";

            query.ProjectedFields =

            "<Field Name='Email' Type='Lookup' List='courseLecturers' ShowField='Email'/>";

            query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"Lecturer\" />" +

            "<FieldRef Name=\"Email\" /><FieldRef Name=\"TargetGroup\" />";

            joinedResults = listCourses.GetItems(query);

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

            {

                int fieldCount = joinedResults.Fields.Count;

                joinedResultsDataTable = joinedResults.GetDataTable();

            }

            return joinedResultsDataTable;

        }

}

Once we run this application page in the browser, we can see the results in the grid: