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

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 2

  I think you have mistake in this line <FieldRef Name='ID' RefType='Id' />.
  In this line "Name" attribute contain with LookuptypeColumn of list.

pls see this url: http://sharepointfordeveloper.blogspot.in/2011/10/step-by-step-spquery-list-joins.html

Answer 3

it is most likely how you are getting the list that us 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? ie

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.