Join element (View)

Article
06/29/2022

Applies to: SharePoint 2016 | SharePoint Foundation 2013 | SharePoint Online | SharePoint Server 2013

Used to join two lists for queries.

<Join   Type= "LEFT" | "INNER"  ListAlias= "TEXT"></Join>

Elements and attributes

The following sections describe attributes, child elements, and parent elements.

Attributes

Attribute	Description
Type	Required. "LEFT" means a left outer join. "INNER" means an inner join.
ListAlias	Optional. Specifies an alternate name for the foreign list. This can be useful when there are two joins to the same list. Different aliases are needed to differentiate the joins. There is no need to explicitly map the alias onto the real name of the foreign list because joins are only allowed through a lookup field relation, and the foreign list is specified in the Lookup field definition. After this value is defined in a Join element, it is used as the value of a List attribute in two places: - In a FieldRef element that is a child to the Eq element of the Join element. - In a Field element that is a child of a ProjectedFields element.

Attribute

Description

Type

Required. "LEFT" means a left outer join. "INNER" means an inner join.

ListAlias

Optional. Specifies an alternate name for the foreign list. This can be useful when there are two joins to the same list. Different aliases are needed to differentiate the joins.

There is no need to explicitly map the alias onto the real name of the foreign list because joins are only allowed through a lookup field relation, and the foreign list is specified in the Lookup field definition.

After this value is defined in a Join element, it is used as the value of a List attribute in two places:
- In a FieldRef element that is a child to the Eq element of the Join element.
- In a Field element that is a child of a ProjectedFields element.

Child elements

Parent elements

Joins

Occurrences

Minimum: 1
Maximum: The value of the MaxQueryLookupFields property for the SPWebApplication object that contains the primary list that is being queried. The system default is 8.

Remarks

The Join element translates into the SQL JOIN statement. The Join element refactors the Eq element to specify the primary and foreign fields on which to make the join. The primary field must be defined as a Lookup field to the foreign field.

Example

The following is an example of two Join elements; both are left outer joins. CustomerName is a lookup field on an Orders list. It looks up to the ID field of a Customers list. The Customer list, in turn, has a CityName field that is a lookup field to a Cities list.

The first Join element assigns 'customers' as an alias for the Customers list. The Eq element child defines the join using the same source and target fields that constitute the lookup relation. The second Join element assigns 'customerCities' as an alias to the Cities list. It defines the join in parallel to the existing lookup relation between the Customer and Cities lists.

Aliases are valuable for the lists because there can be more than one join to the same list and different aliases are needed to differentiate the joins. For example, in addition to the joins from Orders to Customer and from Customer to Cities, there could also be joins from Orders to Suppliers and from Suppliers to Cities. In the case of the last join, a different alias, say 'supplierCities', would be assigned to the Cities list from the one that is used for the Customer to Cities join.

<Joins>
  <Join Type='LEFT' ListAlias='customers'>
    <Eq>
      <FieldRef Name='CustomerName' RefType='Id' />
      <FieldRef List='customers' Name='ID' />
    </Eq>
  </Join>
  <Join Type='LEFT' ListAlias='customerCities'>
    <Eq>
      <FieldRef List='customers' Name='CityName' RefType='Id' />
      <FieldRef List='customerCities' Name='ID' />
    </Eq>
  </Join>
</Joins>

Share via