Can someone provide examples and direction with relational list?

Question

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
                }
              }
            }

Answer 1

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

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.

Answer 2

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

Answer 3

• 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