SPView and Joins

In addition to SPQuery, Joins also can be applied to SPView.

Here, with the same lists (clients, cities, countries) and CAML as in my previos article : https://edanan.wordpress.com/2012/04/28/sample-caml-joins-for-more-than-2-lists/

, I’ll tried it to SPView which name is “JoinView”, and the result is :

The effect is Ribbon becomes disabled. Don’t know why, If anyone knows, please ..

And here’s my project source for that sample : SPVIEW.zip. Please remember to create the corresponding lists and the “JoinView” view first before deploy the solution.

CAML Joins for more than 2 Lists

Here, I’ll show you sample code that use CAML joins for 3 lists. Here are the lists that I used: clients, cities, and countries. Each is connected through lookup column. As you can see below:

And here is the codes:

XmlDocument camlDocument = new XmlDocument();
camlDocument.LoadXml(
    @"<Where> <Eq> <FieldRef Name='clientpresident' /> <Value Type='Text'>[User]</Value> </Eq> </Where>".Replace("[User]", @"sby"));

string siteURL = "http://dnpsps2010/SitePages/Home.aspx";
using (SPSite site = new SPSite(siteURL))
using (SPWeb web = site.OpenWeb())
{
    SPList contact = web.Lists["clients"];
    SPQuery query = new SPQuery();
    query.Query = camlDocument.InnerXml;
    query.Joins =
        @" <Join Type='LEFT' ListAlias='cities'> <Eq> <FieldRef Name='city' RefType='Id'/> <FieldRef List='cities' Name='ID'/> </Eq> </Join> <Join Type='LEFT' ListAlias='countries'> <Eq> <FieldRef List='cities' Name='country' RefType='Id'/> <FieldRef List='countries' Name='Id'/> </Eq> </Join> ";
    query.ProjectedFields =
        @" <Field Name='clientareacode' Type='Lookup' List='cities' ShowField='areacode' /> <Field Name='clientpresident' Type='Lookup' List='countries' ShowField='President' /> ";
    query.ViewFields = @"<FieldRef Name='Title' /><FieldRef Name='city' /><FieldRef Name='clientareacode' /><FieldRef Name='clientpresident' />";
    SPListItemCollection items = contact.GetItems(query);
    foreach (SPListItem itm in items)
    {
        string str1 = string.Empty;
        string str2 = string.Empty;
        if (itm["city"] != null)
        {
            SPFieldLookupValue slv1 = new SPFieldLookupValue(itm["city"].ToString());
            str1 = slv1.LookupValue;
        }
        if (itm["clientareacode"] != null)
        {
            SPFieldLookupValue slv2 = new SPFieldLookupValue(itm["clientareacode"].ToString());
            str2 = slv2.LookupValue;
        }
        Console.WriteLine(itm["Title"] + "; " + "; " + str1 + "; " + str2);
    }
}

That sample code is searching through every clients that it’s president is “sby”, and then displaying the client title, the city, and the area code to the console like below :

One thing to remembered is not all field types that could be used in Projected Fields, only the following:

• Calculated (treated as plain text)
• ContentTypeId
• Counter
• Currency
• DateTime
• Guid
• Integer
• Note (one-line only)
• Number
• Text

For the detail references, read this http://msdn.microsoft.com/en-us/library/ee539975.aspx.

And here is the project source: CAMLJoins.zip, if you don’t want to be bothered.