I'm trying to figure out the best course of action concerning relational lists. I have create several parent and child related lists but I'm having a bit of trouble understanding how to build my CAML query from both directions. 1. Have Parent - Query for Children 2. Have Child - Query for Parent

I have found very limited examples. Can someone show examples of the following in a CAML structure?

  1. How to query and return related Child list items using the Parent information.
  2. How to query and return the Parent using the related Child information.

I realize the CAML query is built of the following, but I really just need help on how to connect it all together from either direction.

            using (SPWeb _impWeb1 = _impSite.OpenWeb(_relativeWebUrl)) {
              SPList _theList1 = _impWeb1.Lists.TryGetList(_listTitle);

              if (_theList != null) {
                SPQuery _theQuery = new SPQuery();

                _theQuery.Query = "";
                _theQuery.Joins = "";
                _theQuery.ProjectedFields = "";
                _theQuery.ViewFields = "";

                SPListItemCollection _theListItems = _theList.GetItems(_theQuery);

                if (_theListItems != null) {
                  // Do Something
  • 1
    Next time, I would recommend you to create two separate questions. Commented Sep 3, 2011 at 18:07

3 Answers 3


For clarity, consider you have a customized Contacts list, where you replaced the Company column with a lookup column, which points to Companies list:

two lists

And, now you're trying to fetch data from Companies list by Company column.

Ok, yes, it is possible (to be precisier, it's easy), and could be performed using following code:

var lookupField = (SPFieldLookup)listItem.Fields["Company"];
var companiesListGuid = new Guid(lookupField.LookupList);

var lookupFieldValue = new SPFieldLookupValue(listItem["Company"]);
var idInCompaniesList = lookupFieldValue.LookupId;

var companiesList = web.Lists[companiesListGuid];
var companyItem = companiesList.GetItemById(idInCompaniesList);

So, the point here is, that to get lookup id information from a lookup field value, you should instatiate the SPFieldLookupValue object. And to get lookup list GUID, you should cast the field to SPFieldLookup.

After we have these values, we can easily instantiate list object and get the item from it using GetItemById method.

Once you've fetched the item, you can easily get any information from it, for example, to get Status value, you should write:

var status = companyItem["Status"];

and so on.

Now, the second part of the question.

Consider, you want to get all items in Contacts list, where Company field value is equal to "Microsoft".

It could be performed using following CAML query:

      <FieldRef Name="Company"  />
      <Value Type="Lookup">Microsoft</Value>

You need to pass this query to Contacts list through SPQuery object in conjunction with SPList.GetItems method.

And, if you have ID of the lookup item (not the value), you should modify the query and use LookupId attribute.

      <FieldRef Name="Company" LookupId="TRUE" />
      <Value Type="Integer">10</Value>

By the way, if you don't like to produce CAML queries in typo-prone strings, as do I, I recommend you to have a look at Camlex.Net. It is a well-known project, which helps to simplify CAML query creation.

  • +1 An alternative to (SPFieldLookupValue)listItem["Company"] is new SPFieldLookupValue(listItem["Company"]) which I believe is the preferred method, given that you have to use SPFieldUserValue this way (casting wouldn't provide the required SPWeb parameter).
    – Stu Pegg
    Commented Sep 4, 2011 at 8:45
  • Huge thanks for your comment! You're right, I confess :( Commented Sep 4, 2011 at 15:00
  • Thanks everyone, this is definitely what I needed to point me in the right direction. This is one of the many reasons why I like this site.
    – Patrick
    Commented Sep 6, 2011 at 15:42
  • @Patrick, btw, to thank somebody, you have "upvote" button :) And to point that the answer is correct, you have "accept" button ;) Commented Sep 8, 2011 at 12:00

This isn't a direct answer to your question, but I would use LINQ to SharePoint to access the data, and then view the CAML generated. :)

This TechEd 2010 session might help with this approach: Developing with REST and LINQ in Microsoft SharePoint 2010

  • Child -> Parent : During Lookup column creation , there is an option to add additional relation fields, in this case no joins are required we can simply reference the columns in viewfields without any joins
  • Parent to Child : we need to use _theQuery.Query , I haven't tried this yet but link http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.joins.aspx can help you get there. I will try it later and update the answer
  • Thanks for getting back to me but concerning the Child -> Parent piece. I guess what I was trying to get at was if I know the child, how can I get the parent. I do realize there is the option to create additional relation fields and I've written code to do that. I'm just confused on the part of how to query for the parent using the child information.
    – Patrick
    Commented Sep 2, 2011 at 22:48
  • It would be neat to see a complete practical example approaching this from both directions. Even if the SPFieldLookup is say "Title" for a customer and the children are orders. I've seen bits and pieces of this but never a real full example. Any help would be really appreciated!
    – Patrick
    Commented Sep 2, 2011 at 22:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.