Join 2 lists in SharePoint using CAML query

Question

I have only been using SharePoint for a few days and I know this question has been asked many times before but I've tried all of them and none of them work.

These are my design

• Document as a Document Library type have an Id

  Id | DocumentName
  ---+-------------
  1  | Document A
  2  | Document B
  

• Activities list, have a foreign key 'DocId' that reference to the Document Id

  Id | DocId | Name
  ---+-------+-----------
  1  | 1     | Activity A
  2  | 1     | Activity B
  3  | 1     | Activity C
  4  | 2     | Activity D
  

The problem is I need to get all the Activities that included the document name on the Document.

In SQL I can use Join query to get the additional information from another table. But I have tried some Join statement in the CAML query and none of them work. Here is the result that I need to get.

Id | DocId | Name       | DocumentName
---+-------+--------------------------
1  | 1     | Activity A | Document A
2  | 1     | Activity B | Document A
3  | 1     | Activity C | Document A
4  | 2     | Activity D | Document B

Could anyone please suggest me the query. Here is my query

<View>
  <ViewFields>
    <FieldRef Name = 'DocLeafRef'/>
    <FieldRef Name = 'ID'/>
    <FieldRef Name = 'e8_document'/>
    <FieldRef Name = 'Title'/>
    <FieldRef Name = 'Author'/>
    <FieldRef Name = 'Created'/>
  </ViewFields>
  <Joins>
    <Join Type = 'INNER' ListAlias = 'Documents'>
      <Eq>
         <FieldRef Name ='e8_document' RefType = 'Id'/>
         <FieldRef Name ='ID' List ='Documents'/>
      </Eq>
    </Join>
  </Joins>
  <ProjectedFields>
    <Field ShowField ='FileLeafRef' Type ='Lookup' Name ='DocLeafRef' List ='Documents'/>
  </ProjectedFields>
  <Query>
     <Where>
        <Eq>
           <FieldRef Name='e8_caseId'></FieldRef>
           <Value Type = 'Number'>23</Value>
        </Eq>
     </Where>
  </Query>
</View>

I'm querying on the Activities list and the e8_document is the lookup field that reference to the Id in the Document. As i understand, we need the projection and i've added the ProjectedFields but the query throws an error.

Answer 1

SPQuery query = new SPQuery();

query.Joins = "<Join Type='INNER' ListAlias='Documents'>" +
                "<Eq>" +
                    "<FieldRef Name='e8_document' RefType='Id'/>" +
                    "<FieldRef List='Documents' Name='ID'/>" +
                "</Eq>" +
             "</Join>";

query.ProjectedFields = "<Field Name='DocName' Type='Lookup' List='Documents' ShowField='DocumentName'/>";

query.ViewFields = "<FieldRef Name='DocName'/>";

SPList lstActivities = web.Lists["Activities"];
SPListItemCollection items = customerList.GetItems(query);

Use 'Id' instead of 'Lookup' in Join of query.