Home > CAML, Sharepoint > CAML Joins for more than 2 Lists

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:

image

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 :

image

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.

Categories: CAML, Sharepoint Tags: ,
  1. Mykle
    July 13, 2012 at 1:07 pm

    Hi ! Thanks for your post, it really helps !

    But I have another configuration that makes me some trouble…

    I have three lists, the two firsts of them (listitem1 & listitem2) have some standards fields (no lookup). But the third one, which is kinda between them, has the following fields:
    – title
    – lookup listitem1 id
    – lookup listitem2 id

    My question is : how can i get the elements of the listitem2 from the listitem1 ? The only thing i made is to get elements of the listitem1 or listitem2, from the listitem3. And in your case, your configuration is sequential (clients–>cities–>countries) so I can’t reproduce your solution…

    Thank you in advance for your help.

    • July 13, 2012 at 1:51 pm

      You can Join Query only from the List where there is lookup field, that’s 3rd list.
      So you must go from 3rd list to get 1st and 2nd list data.
      Sorry, that’s all I could think for now.
      Thanks.

  1. April 28, 2012 at 6:08 pm
  2. April 29, 2012 at 4:53 pm
  3. April 29, 2012 at 5:02 pm

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

%d bloggers like this: