SPQuery.Joins Property
Gets or sets the Joins element section of the query including all its child elements.
Namespace: Microsoft.SharePoint
Assembly: Microsoft.SharePoint (in Microsoft.SharePoint.dll)
Available in Sandboxed Solutions: Yes
Available in SharePoint Online
Syntax
'Declaration
Public Property Joins As String
Get
Set
'Usage
Dim instance As SPQuery
Dim value As String
value = instance.Joins
instance.Joins = value
public string Joins { get; set; }
Property Value
Type: System.String
The Joins element XML.
Remarks
Each join is represented by a Join element child of the Joins element. Only inner and left outer joins are permitted. Moreover, the field in the primary list must be a Lookup type field that looks up to the field in the foreign list. There can be joins to multiple lists, multiple joins to the same list, and chains of joins. If a given list is the foreign list in more than one join, it must have distinct aliases assigned to it by the ListAlias attributes of the Join elements representing the joins.
Examples
The following is an example of a Joins element with two 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>