Join 2 lists in share point using CAML query

Question

I just use Sharepoint for few days and I know this question have asked many times but I've tried all of them and none of them work.

These are my design

Document as a Document Library type have a 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 try some Join statement in 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 any one 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 query 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 projection but the query throws an error.

Can you post the CAML query that you've tried which gave you the above result? Then we can tell you how to correct it using list joins and projections. — Thriggle
@Thriggle I add my query, could you please help me whats wrong with the query — Hiep Lam

Vadim Gremyachev Vadim Gremyachev · Accepted Answer · 2017-01-27T13:42:10

It seems the Query element is invalid in your case, basically Query element could not contain Joins as a child element.

Server-Side Object Model

For SPQuery Class Joins element needs to be specified via SPQuery.Joins Property:

var qry = new SPQuery();
qry.Joins = @"<Join Type="LEFT" ListAlias="Documents">
            <Eq>
                <FieldRef Name="DocId" RefType="ID" />
                <FieldRef Name="ID" List="Documents" />
            </Eq>
        </Join>";

Client-Side Object Model

Here is the complete CAML query for CSOM API:

<View>
    <ViewFields>
        <FieldRef Name="ID" />
        <FieldRef Name="DocId" />
        <FieldRef Name="Name" />
        <FieldRef Name="Documents" />
    </ViewFields>
    <Joins>
        <Join Type="LEFT" ListAlias="Documents">
            <Eq>
                <FieldRef Name="DocId" RefType="ID" />
                <FieldRef Name="ID" List="Documents" />
            </Eq>
        </Join>
    </Joins>
    <ProjectedFields>
        <Field ShowField="DocumentName" Type="Lookup" Name="Documents" List="Documents" />
    </ProjectedFields>
    <Query />
</View>

Join 2 lists in share point using CAML query

1 Answers