Multiple Inner Joins CAML query

Question

I'm trying to convert the following SQL statement to a CAML query:

SELECT        t.Id, t.Name, t.CustomerId
FROM            Ticket AS t 
              INNER JOIN
                         Customer AS c1 ON t.CustomerEMail = c1.EMail
              INNER JOIN
                         Customer AS c2 ON c1.CompanyNo = c2.CompanyNo
WHERE        (c2.Email = 'client1@co1.com')

Using CAMLJS, I got this far: (CompanyNo is equivalent to Nav_CustomerNo)

var query = new CamlBuilder()
.View(["Title", ...])
.InnerJoin("ClientLookup", "c1")
.Select("EMail", "c1Email")
.InnerJoin("ClientLookup", "c2")
.Select("Nav_CustomerNo", "c2CompanyNo")
.Query()
.Where()
.All()
.ToString()

But I'm not sure how to proceed from here. How do I convert the

Customer AS c1 ON t.CustomerEMail = c1.EMail

line? I am thinking something like: .Where("c1Email").EqualTo(??

Here is the equivalent CAML:

<View>
    <ViewFields>
        <FieldRef Name="Title" />
        <FieldRef Name="Ticket_MainBody" />
        <FieldRef Name="Ticket_SupportID" />
        <FieldRef Name="ClientLookup" />
        <FieldRef Name="IsPrivateTicket" />
        <FieldRef Name="Ticket_IsFAQ" />
    </ViewFields>
    <Joins>
        <Join Type="INNER" ListAlias="c1">
            <Eq>
                <FieldRef Name="ClientLookup" RefType="ID" />
                <FieldRef Name="ID" List="c1" />
            </Eq>
        </Join>
        <Join Type="INNER" ListAlias="c2">
            <Eq>
                <FieldRef Name="ClientLookup" RefType="ID" />
                <FieldRef Name="ID" List="c2" />
            </Eq>
        </Join>
    </Joins>
    <ProjectedFields>
        <Field ShowField="EMail" Type="Lookup" Name="c1Email" List="c1" />
        <Field ShowField="Nav_CustomerNo" Type="Lookup" Name="c2CompanyNo" List="c2" />
    </ProjectedFields>
    <Query>
        <Where />
    </Query>
</View>

Answer 1

Just spent a considerable time this morning trying to work this out and I think I have a solution for you.

First - let's start with some generic pseudo structure:

ParentTable: Id, Title, ChildTableReference

ChildTable: Id, Title, BabyTableReference

BabyTable: Id, Title

So, we have a parent table that contains a reference. The table tied to that reference contains a reference to another table under that.

Here is a simple practical example:

(parent) CustomerOrder: Id, Title, CustomerID

(child) Customer: Id, Title, RegionID

(baby) Region: Id, Title

So, what if we wanted the following query response:

CustomerOrder_Id, CustomerOrder_Title, Customer_Title, Region_Title

The first part of the XML is easy:

    <ViewFields>
        <FieldRef Name='ID' />
        <FieldRef Name='Title' />
        <FieldRef Name='Customer_Title' />
        <FieldRef Name='Region_Title' />
    </ViewFields>        

But how do we stitch together the tables? We use joins.

The first Join is very simple:

    <Join Type='INNER' ListAlias='Customers'>
        <Eq>
            <FieldRef Name='CustomerID' RefType='ID' />
            <FieldRef List='Customer' Name='ID' />
        </Eq>
    </Join>

This first join is ok and if all we wanted to do was view the customer name we would just have to add our Projection and we would be good to go. But, we also want the Region of this customer. For this, we need a second join. The trick to the second join is to add a list reference to the FIRST FieldRef statement. The exact line is: FieldRef List='Customers' Name='RegionID' RefType='ID' . Notice that the List= attribute points to the alias of the Child reference. Put another way, there is a reference list called Customers that contains a column called RegionID that is a reftype of ID. That needs to be equal to our new aliased table called Regions based on the root table Region on the ID attribute.

  <Join Type='INNER' ListAlias='Regions'>
      <Eq>
          <FieldRef List='Customers' Name='RegionID' RefType='ID' />
          <FieldRef List='Regions' Name='ID' />
      </Eq>
  </Join>

We are almost home now. At this point we need to create Projected Fields to spit out the Region.Title and Customer.Title attributes.

  <ProjectedFields>
        <Field ShowField='Title' Type='Lookup' Name='Customer_Title' List='Customers' />
        <Field ShowField='Title' Type='Lookup' Name='Region_Title' List='Regions' />

    </ProjectedFields>

Putting it all together then, your CAML query would be:

//Completed query
<View>
   <ViewFields>
        <FieldRef Name='ID' />
        <FieldRef Name='Title' />
        <FieldRef Name='Customer_Title' />
        <FieldRef Name='Region_Title' />
    </ViewFields>
    <Joins>
        <Join Type='INNER' ListAlias='Customers'>
            <Eq>
                <FieldRef Name='CustomerID' RefType='ID' />
                <FieldRef List='Customer' Name='ID' />
            </Eq>
         </Join>
         <Join Type='INNER' ListAlias='Regions'>
            <Eq>
                <FieldRef List='Customers' Name='RegionID' RefType='ID' />
                <FieldRef List='Regions' Name='ID' />
            </Eq>
         </Join>
    </Joins>
    <ProjectedFields>
        <Field ShowField='Title' Type='Lookup' Name='Customer_Title' List='Customers' />
        <Field ShowField='Title' Type='Lookup' Name='Region_Title' List='Regions' />
    </ProjectedFields>
    <Query />
</View>

That should get you all the way there. Also - you can query (Where clause for instance) on any element you've joined to provided the field type is supported (text, refid, number, etc). Just remember that if you are going to query on say, the ID of the Region you will need to add the ID to your ProjectedFields.

// completed query with WHERE clause filtering by BABY table ID.
<View>
   <ViewFields>
        <FieldRef Name='ID' />
        <FieldRef Name='Title' />
        <FieldRef Name='Customer_Title' />
        <FieldRef Name='Region_Title' />
    </ViewFields>
    <Joins>
        <Join Type='INNER' ListAlias='Customers'>
            <Eq>
                <FieldRef Name='CustomerID' RefType='ID' />
                <FieldRef List='Customer' Name='ID' />
            </Eq>
         </Join>
         <Join Type='INNER' ListAlias='Regions'>
            <Eq>
                <FieldRef List='Customers' Name='RegionID' RefType='ID' />
                <FieldRef List='Regions' Name='ID' />
            </Eq>
         </Join>
    </Joins>
    <ProjectedFields>
        <Field ShowField='Title' Type='Lookup' Name='Customer_Title' List='Customers' />
    <Field ShowField='ID' Type='Lookup' Name='Region_ID' List='Regions' />
        <Field ShowField='Title' Type='Lookup' Name='Region_Title' List='Regions' />
    </ProjectedFields>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='Region_ID' LookupId='True' />
                <Value Type='Integer'>1</Value>
            </Eq>
        </Where>
    </Query>
</View>