Sharepoint 2010 SPQuery to join two custom lists

Question

I have two custom lists.

list 1 having following columns:
ID (consider as PK)
title

list 2 having following columns
NID (lookup column)
title

I'm using spquery.joins like below

<Joins> 
  <Join Type='INNER' ListAlias='list2'> 
    <Eq> 
      <FieldRef Name='ID' RefType='Id' /> 
      <FieldRef List='list2' Name='NID' />  
    </Eq> 
  </Join> 
</Joins>

I'm defining projected fields as well like below

"<Field Name='NID' Type='Lookup' List='list2' ShowField='NID'/>";

I don't know what is wrong with this but whenever I try I get following exception

Value doesn't fall within the expected range

Answer 1

it is most likely how you are getting the list that is causing the issue.

Can you paste the code that calls this CAML query just to see? But first off are you calling the results via the InternalName?

But I believe the answer is:

results[results.Fields.GetField(columnName).InternalName]

OR

You have the ViewFields property on, which will also cause this error with this code. The code looks fine however as it is.

Answer 2

An example of JOIN:

SPQuery oQuery = new SPQuery();
StringBuilder sbJoin = new StringBuilder();
StringBuilder sbProj = new StringBuilder();

oQuery.Query = "<OrderBy><FieldRef Name='Title' /></OrderBy>";

using (SPSite siteCollection = new SPSite("http://localhost/"))
{
    using (SPWeb web = siteCollection.OpenWeb())
    {
        SPList oList = web.Lists["List1"];
        SPListItemCollection items = null;

        sbJoin.Append("<Join Type='INNER' ListAlias='List2'>");
        sbJoin.Append("<Eq>");
        sbJoin.Append("<FieldRef Name='List2' RefType='Id'/><FieldRef List='List2' Name='ID'/>");
        sbJoin.Append("</Eq>");
        sbJoin.Append("</Join>");


        sbProj.Append("<Field Name='List2NID' Type='Lookup' List='List2' ShowField='NID'/>");
        sbProj.Append("<Field Name='List2ContentTypeId' Type='Lookup' List='List2' ShowField='ContentTypeId' />");

        oQuery.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='List2NID'/>";
        oQuery.Joins = sbJoin.ToString();
        oQuery.ProjectedFields = sbProj.ToString();

        items = oList.GetItems(oQuery);

        if (items.Count > 0)
        {
            foreach (SPListItem item in items)
            {
                Console.WriteLine("Title: {0}, List2 NID: {1}",
                        item["Title"],
                        item["List2NID"]);
            }
        }
    }
}

Recalling that is only possible using simple join with columns as text, number and datetime columns ... with another type could give this same error

Answer 3

I think you have mistake in this line <FieldRef Name='ID' RefType='Id' />.

In this line "Name" attribute contain with LookuptypeColumn of list. See this url: http://sharepointfordeveloper.blogspot.in/2011/10/step-by-step-spquery-list-joins.html

Answer 4

I must say that in my experience using CAML for Joining falls very quickly short in its usage. Not sure if this is answering your question, but in my experience, relying on a Content type used in both targeted lists, than using a SPSiteDataQuery (many example available on the web, e.g. http://www.c-sharpcorner.com/uploadfile/Roji.Joy/cross-site-list-query-using-spsitedataquery-in-sharepoint-2010/) to query for the Content type (e.g. using Content type Id as one of the filter conditions) + limiting which lists are actually participating (using the Lists property -http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.lists.aspx), to which you would get the benefit of being able to state (via ViewFields) which fields to be retrieved - for performance reasons, and same obvious features (WHERE, ORDER, LIMIT, etc. clauses) resulted in fastest, more predictable.

Add to this the fact that you can also target cross sub-sites these lists, and combine with caching of results via CrossListQueryCache (see here a comparison http://gadekarsatish.blogspot.co.at/2013/03/query-sharepoint-list-spquery-vs.html) - i would say this gives you the answer!