Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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
                }
              }
            }
share|improve this question
1  
Next time, I would recommend you to create two separate questions. –  Andrey Markeev Sep 3 '11 at 18:07
add comment

3 Answers 3

up vote 2 down vote accepted

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:

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

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.

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

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.

share|improve this answer
    
+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). –  Stuart Pegg Sep 4 '11 at 8:45
    
Huge thanks for your comment! You're right, I confess :( –  Andrey Markeev Sep 4 '11 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 Sep 6 '11 at 15:42
    
@Patrick, btw, to thank somebody, you have "upvote" button :) And to point that the answer is correct, you have "accept" button ;) –  Andrey Markeev Sep 8 '11 at 12:00
add comment

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

share|improve this answer
add comment
  • 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
share|improve this 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 Sep 2 '11 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 Sep 2 '11 at 22:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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