Step by Step SPQuery List joins

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";

            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.

Step by Step SPQuery List joins

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";

            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.

How to Use CAML Query to get Data from SharePoint 2007 List

Simple Products List in SharePoint 2007. The list was pretty simple with 2 columns "Product" and "Cost". I used U2U to create a pretty descent CAML query. I created a simple website in VS2008 to test it !!! It all worked fantastically!!!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SharePoint;

namespace WebApplication1
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SPList mylist=new SPSite ("http://sharepointblog").OpenWeb ().Lists ["Products"];
SPQuery query=new SPQuery ();
query.Query = "";
SPListItemCollection items=mylist.GetItems (query);

foreach (SPListItem proditem in items )
{
Response.Write(proditem["Title"].ToString ());
Response.Write(proditem["Cost"]);

}


}
}
}