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

Current situation:
=============
In my example, I've got 2 custom lists:

  • Accounts
  • Addresses

In the "Accounts"-list I've created 2 lookup columns to the "Addresses"-list

  • Address1
  • Address2

When I try to create a CAML query, I can succesfully get listitems with each join separately:

<Join Type='INNER' ListAlias='Addresses'>
    <Eq>
        <FieldRef Name='Address1' RefType='Id'/>
        <FieldRef List='Addresses' Name='ID'/>
    </Eq>
</Join>

But when I try to add a second one (for Address2) WITH different ListAlias it throws an exception:

<Join Type='INNER' ListAlias='Address1'>
    <Eq>
        <FieldRef Name='Address1' RefType='Id'/>
        <FieldRef List='Addresses' Name='ID'/>
    </Eq>
</Join>
<Join Type='INNER' ListAlias='Address2'>
    <Eq>
        <FieldRef Name='Address2' RefType='Id'/>
        <FieldRef List='Addresses' Name='ID'/>
    </Eq>
</Join>

ERROR:
=============

{System.ArgumentException: Value does not fall within the expected range.
 at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)
 at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)
 at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
 at Microsoft.SharePoint.SPListItemCollection.get_Count()
 at ...}

Question:
=============
Does anyone have any idea how to solve this?

Changing the ListAlias to Address1 and Address2 does not work either... but according to this: http://msdn.microsoft.com/en-us/library/ee535061(v=office.14).aspx it should...

share|improve this question
    
Important notice: Multilookup is perfectly supported in CAML queries (if you use SPQuery or client-side CamlQuery). There are even several specialized elements like Includes and NotIncludes which allow to work with multilookups. Though multilookups are not supported in SPSiteDataQuery and it's descendants. –  Andrey Markeev Mar 4 '13 at 12:38
    
Is there an example of this? If you look to this site's anwser, they say it isn't possible social.technet.microsoft.com/Forums/en-PH/… –  Ruben Herman Mar 4 '13 at 13:20
1  
This only applies to "Join" element, but you can still use MultiLookups e.g. in "Where" clause. Perhaps in your case MultiLookup column cannot be used, true, but I just wanted to point out that this comment "(PS...why not multi-lookup??? -> multilookup is not supported in CAML queries)" is not accurate. It is supported, though in not all cases. –  Andrey Markeev Mar 4 '13 at 13:38
    
Ok, thanks for clearing that up! Updated the post ;-) –  Ruben Herman Mar 4 '13 at 13:40
    
And I think most likely you can achieve your goal without any "Joins", just by adding dependent lookup columns. –  Andrey Markeev Mar 4 '13 at 13:43
show 1 more comment

2 Answers

up vote 2 down vote accepted

Just in case you will not be able to struggle through the "Join" thing, please consider those alternative solutions:

1. Separate queries

Why not query the lists separately and then merge them manually? If you need to operate with the lists programmatically, I think this would be the best approach.

First, select all items from the Accounts list (or filter them based on your requirements, if any). Then, grab all IDs for Address items, and fetch them from Addresses list. Finally, merge them using Linq.

Tentative code (not tested, and I use multilookup here for Addresses column):

var items = accountsList.GetItems();
var ids = items
    .Cast<SPListItem>()
    .SelectMany(li => new SPFieldLookupValueCollection(li["Addresses Field"] as String).Cast<SPFieldLookupValue>().Select(lv => lv.LookupId))
    .Distinct()
    .ToArray();

var query = new SPQuery() {
    Query = "<Where><In><FieldRef Name=\"ID\"><Values>" + string.Join("", ids.Select(id => "<Value Type=\"Counter\">" + id + "</Value>") + "</Values></Where>"
};

var addressItems = addressesList.GetItems(query);

var joinedList = items.Cast<SPListItem>().Select(item => new
{
  Item = item,
  Addresses = addressItems
     .Cast<SPListItem>()
     .Where(li => new SPFieldLookupValueCollection(item["Addresses Field"] as String).Cast<SPFieldLookupValue>().Select(lv => lv.LookupId).Contains(Convert.ToInt32(li["ID"]))).ToArray()
}).ToArray();

If you add some abstractions/helper methods/method extensions to the code above, it will look much more pretty and it will do the work. If you will have much data there, it might be you will have to process the Addresses list in several steps, or something like this, but anyway...

2. Dependent Lookups

The second idea is to use dependent lookups instead of joins. Please, keep in mind that there are some important limitations to this approach (details below).

When you create a lookup field, you can pull along some other columns from the target list:

enter image description here

On the screenshot you can see that I've checked Postal code and CountryText fields to be pulled into my "Accounts" list.

Result will be the following:

enter image description here

So, you've got some additional columns which contain the required data. In SharePoint API, these columns are called dependent lookups.

Admittedly, they don't look very nice, but they do the work: data is here, without any joins. The view can easily be fixed to look more pretty using XSLT or Client rendering (in SP2013).

So now if you want same data in your code, you can get the contents of the view using SPList.GetItems(SPView) method, or you can reference the dependent lookup columns in your SPQuery.

Limitations

Unfortunately, lookup columns have some limitations. They can target only limited number of field types:

1.Counter (usually ID field) 2.Text (Single line of text) 3.Number 4.DateTime 5.Computed (only if SPFieldComputed.EnableLookups is set to true) 6.Calculated (only if it's Output type is equal to Text)

See this answer for details.

You can still include some types of fields by referencing them via Calculated columns, as I referenced CountryText column in my example above (the original "Country" column in of type "Choice", and thus cannot be used directly as a target of lookup):

enter image description here

Unfortunately, Calculated columns also have their limitations regarding accepted column types...

share|improve this answer
    
Thanks for this Andrey, I'll keep this in mind. Preferably I would choose the dependent lookup columns if the joins don't work, but my only concern with this is I'll get to the column treshold (Lookup: max 96) since I have another table Contacts that will inherit from Accounts (and Accounts from Addresses), Address allready has +25 columns, Accounts also -> that's allready +75 columns –  Ruben Herman Mar 4 '13 at 15:30
    
As for the first solution... this is a great one, but I need to query from different datasources and compare the data. It would be better if I got ALL needed data at once, since we've had performance issues in the past –  Ruben Herman Mar 4 '13 at 15:33
    
Wow, 75 columns is really a great many! I'm afraid the second approach will not do. "List View Lookup Thresold" is by default 8 (see this post for details: sympmarc.com/2012/07/23/…), and big number of lookup columns in fact often cause performance issues, though I'm not sure if that concerns dependent lookups. But please test this before you start actual implementation. Since there are so many columns, I would say the first approach is even better. Two simple queries often perform better than a single heavy one. –  Andrey Markeev Mar 4 '13 at 18:18
add comment

According to your link you need different ListAlias attributes:

ListAlias: Optional. Specifies an alternate name for the foreign list. This can be useful when there are two joins to the same list. Different aliases are needed to differentiate the joins.

On similar problems I try to get the result af the two queries (CrossListQueryInfo) and merge the two results outside CAML:

results.Rows.Add(secondResults.Rows[i].ItemArray);

When all the items are in the same list, you can sort the items using Linq:

var tempresult = (from DataRow items in results.Rows
                  orderby items["Created"] descending
                  select items
                  ).Take(10);

results = tempresult.CopyToDataTable();

Kirk Evans wrote a good blog on Caml list joins using SPQuery:

The first thing to notice is the SPQuery.Joins property which lets me provide 1 or more joins for the list. The list must already have a lookup column and a relation defined to the list being joined to. Notice in the Joins property that we refer to the name that we provided when we created the lookup column, “Manager”.

The second thing to notice is the ProjectedFields property. This is where we tell SharePoint how to project the lookup columns into the result. In a contacts list, the “First Name” column has an internal name of “FirstName”. The “Last Name” column is actually the Title column (see above for how to determine the .InternalName of a field).

Finally, the ViewFields property lets us define which fields are included in the result. We use the same name that we used in the ProjectedFields property. This name could be anything, so long as the name in ProjectedFields and the name in ViewFields match.

using (SPSite site = new SPSite("http://spstc.sharepoint.com"))
{
    SPWeb web = site.RootWeb;

    SPQuery query = new SPQuery();

    query.Joins = "<Join Type='INNER' ListAlias='DSE'>" +
                    "<Eq>" +
                        "<FieldRef Name='Manager' RefType='Id'/>" +
                        "<FieldRef List='DSE' Name='ID'/>" +
                    "</Eq>" +
                    "</Join>";
    query.ProjectedFields =
        "<Field Name='DSEFirstName' Type='Lookup' " +
                "List='DSE' ShowField='FirstName'/>" +
        "<Field Name='DSELastName' Type='Lookup' " +
                "List='DSE' ShowField='Title'/>";

    query.ViewFields = "<FieldRef Name='Title'/>" +
                        "<FieldRef Name='DSEFirstName'/>" +
                        "<FieldRef Name='DSELastName'/>";
    SPList customerList = web.Lists["Projects"];
    SPListItemCollection items = customerList.GetItems(query);
    foreach (SPListItem item in items)
    {
        SPFieldLookupValue dseLastName =
            new SPFieldLookupValue(item["DSELastName"].ToString());
        SPFieldLookupValue dseFirstName =
            new SPFieldLookupValue(item["DSEFirstName"].ToString());

        Console.WriteLine("{0}  {1}   {2}",
                item.Title,
                dseLastName.LookupValue,
                dseFirstName.LookupValue);
    }
}
share|improve this answer
    
Do I also have to use my new listname in my query? –  Ruben Herman Mar 4 '13 at 13:07
    
@RubenHerman Yes, as I understand it. And the lookup column already have to be in the list to be joined. –  Benny Skogberg Mar 4 '13 at 13:24
    
It's allready in my list. The only guess why this doesn't work is because I use the same field in both lists and when using an alias, the field from my query where I search on, isn't found anymore... I'll try updating the query first –  Ruben Herman Mar 4 '13 at 13:31
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.