Joins in CAML Query

Finally in SharePoint 2010, you can have Joins on List using CAML.

Here is an example:

For Test purpose I have created four lists having relation between them. i.e. Cities, Customers, Product and Orders.
The Customers list contains a lookup field pointing to the Cities list to specify in which city a customer lives. The Orders list contains a lookup field to the Customers and Product list to specify who’s the customer of a given order and what product he has ordered. Please make sure you have these relations in place as lookup fields are the basis on which we apply joins in CAML.

1. Declaring List Object: We have created an object of the Customers list from which records are to be fetched.

SPList CustomerList = SPContext.Current.Web.Lists[“Customers“];

2. Setting Join Property: One thing you woul notice below is that SPQuery object has been enhanced with a property named Joins.  This is the property which you need to set.
SPQuery CustomerCityQuery = new SPQuery();
CustomerCityQuery.Joins =

“<Join Type=’INNER’ ListAlias=’Cities‘>” +
“<Eq>” +
“<FieldRef Name=’City‘ RefType=’Id‘ />” +
“<FieldRef List=’Cities‘ Name=’ID‘ />” +
“</Eq>” +
“</Join>”;

The “Join” tag has an attribute named “Type”. It specifies the type of join we intend to have. In my case I have set it to “INNER”, the “ListAlias” property points to the list with which we are having this join i.e. Cities.

In my case, I have a lookup column in my Customers list named City which points to the Id in the list Cities. Hence the first “FieldRef” tag has its “Name” set to City  and “RefType” is set to Id.

The second “FieldRef” tag has attribute “List” set to Cities and “RefType” is set to ID signifying the column of the list on which Join has to be implemented.

3. Setting Projected fields: These are fields in joined lists that can be included in the view when one or more other lists have been joined in a query. In my case I have included CityName, State and Country columns from Cities list while creating lookup from Customers. In the below code I am adding CityName & State to the projected fields property of SPQuery.

StringBuilder ProjectedFields = new StringBuilder();

ProjectedFields.Append(“<Field Name=’CityName‘ Type=’Lookup’ List=’Cities’ ShowField=’CityName‘ />”);
ProjectedFields.Append(“<Field Name=’State’ Type=’Lookup’ List=’Cities‘ ShowField=’State‘ />“);
CustomerCityQuery.ProjectedFields = ProjectedFields.ToString();

4. The code ahead is similar to what we have in normal SPQuery operations without joins.
SPListItemCollection Results = CustomerList.GetItems(CustomerCityQuery);
foreach (SPListItem Result in Results)
{
SPFieldLookupValue CityTitle = new SPFieldLookupValue(Result[“CityName“].ToString());
SPFieldLookupValue CityCountry = new SPFieldLookupValue(Result[“State“].ToString());
Response.Write(string.Format(“Customer {0} lives in {1} – {2}”,
Result.Title,
CityTitle.LookupValue,
CityCountry.LookupValue));
}

This was my first attempt to have joins using CAML for Sharepoint 2010. Will refine this post as and when possible.

Advertisements

2 thoughts on “Joins in CAML Query

  1. I Hava a Document Library[CriticalDocuments] in SiteA. It has a MULTISELECT Lookup to a list called [Companies] in site B. any IDea how to join Companies to CriticalDocuments in such a way that if a CriticalDocument has more than one company, it will appear once for each company?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s