SPQuery List Joins

Posted: January 3, 2014 in SharePoint
Tags: ,
SharePoint 2010 Step by Step SPQuery List joins Using CAML
I am using the example following example lists
  1. CustomerCity List
  2. Customer
List Columns
CustomerCity columns are:
Tilte
Single Line text
Customer columns are:
Title
Single Line text
City
Lookup type of CustomerCity
Dummy Data used
For CustomerCity
Title
City1
City2
City3
For Customer
Title
City
Customer1
City1
Customer2
City1
Customer3
City2
Customer4
City2
customer5
City2
Customer6
<NULL>
Using the Join in SharePoint 2010 List Using SPQuery Class.
We need to set the three most important properties for that.
  1. Joins
  2. ProjectedFields
  3. ViewFields
SharePoint 2010 adds Join To CAML SPQuery.Joins
Types of joins
  1. Inner
  2. Left
Requested Lookup columns
Projections allow inclusion of fields joined lists
SPQeury.ProjectedFields
Joins: Each join is represented by a Join element child of the Joins element. Only inner and left outer joins are permitted. Moreover, the field in the primary list must be a Lookup type field that looks up to the field in the foreign list. There can be joins to multiple lists, multiple joins to the same list, and chains of joins. If a given list is the foreign list in more than one join, it must have distinct aliases assigned to it by the ListAliasattributes of the Join elements representing the joins.
Note: Multiple Lines of text, Choice type columns are not supported in ProjectedFields.
private void button1_Click(object sender, EventArgs e)
        {
            string siteUrl = “http://home&#8221;;
            SPWeb _web = new SPSite(siteUrl).OpenWeb();
            var items =_web.Lists[“Customer”].GetItems(GetQuery());
            foreach (SPListItem item in items)
            {
                MessageBox.Show(string.Format(“{0}—-{1}”, item[“Title”], item[“CityTitle”]));
            }
        }
        private SPQuery GetQuery()
        {
            SPQuery _query = new SPQuery();
            _query.Query = “”;
            _query.Joins = @”<Join Type=’INNER’ ListAlias=’City’>
                          <!–List Name: CustomerCity–>
                          <Eq>
                            <FieldRef Name=’City’ RefType=’ID’ />
                            <FieldRef List=’City’ Name=’ID’ />
                          </Eq>
                        </Join>”;
            _query.ProjectedFields = @”<Field Name=’CityTitle’ Type=’Lookup’ List=’City’ ShowField=’Title’ />
                                    <Field Name=’CityContentTypeId’ Type=’Lookup’ List=’City’ ShowField=’ContentTypeId’ />”;
            _query.ViewFields = @” <FieldRef Name=’Title’ />
                                     <FieldRef Name=’CityTitle’ />”;
            return _query;
        }
The above “Query” property of the SPQuery class I left blank you can enter the condition according to your requirement.
Advertisements

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