Join tables using FetchXml

As described in Query data using FetchXml, start your query by selecting a table using the entity element.

Use the link-entity element to describe the data from related tables to return with your query with the following attributes:

Attribute	Short Description Find more details in the link-entity element reference
name	The logical name of the related table.
from	The logical name of the column from the related table that matches the column specified in the to attribute.
to	The logical name of the column in the parent element to match with the related table column specified in the from attribute.
link-type	The type of link use. Default behavior is inner, which restricts results to rows with matching values in both tables. Other valid values are: - outer - any - not any - all - not all - exists - in - matchfirstrowusingcrossapply Learn about link-type options
alias	Represents the name of the related table in the results.
intersect	Indicates that the link-entity is used to join tables and not return any columns

For example, the following query returns up to 5 records from the account and contact tables based on the PrimaryContactId lookup column in the account record:

<fetch top='5'>
   <entity name='account'>
      <attribute name='name' />
      <link-entity name='contact'
         from='contactid'
         to='primarycontactid'
         link-type='inner'
         alias='contact'>
         <attribute name='fullname' />
      </link-entity>
   </entity>
</fetch>

The results look like this:

 -----------------------------------------------------------------
 | name                             | contact.fullname           |
 -----------------------------------------------------------------
 | Litware, Inc. (sample)           | Susanna Stubberod (sample) |
 -----------------------------------------------------------------
 | Adventure Works (sample)         | Nancy Anderson (sample)    |
 -----------------------------------------------------------------
 | Fabrikam, Inc. (sample)          | Maria Campbell (sample)    |
 -----------------------------------------------------------------
 | Blue Yonder Airlines (sample)    | Sidney Higa (sample)       |
 -----------------------------------------------------------------
 | City Power & Light (sample)      | Scott Konersmann (sample)  |
 -----------------------------------------------------------------

Limitations

You can add up to 15 link-entity elements to a query. Each link-entity adds a JOIN to the query and increases the time to execute the query. This limit is to protect performance. If you add more than 15 link-entity elements to a query, you get this error:

Code: 0x8004430D
Number: -2147204339
Message: Number of link entities in query exceeded maximum limit.

Child elements

Within the link-entity element you can add child elements just like on the parent element to:

• Select columns from the related table
• Filter rows from the related table
• Join another related table

Many-to-one relationships

The previous example is a many-to-one relationship where many account records can refer to a one contact record. This information is defined in the Account account_primary_contact many-to-one relationship, which has the following values:

Property	Value	Comment
SchemaName	account_primary_contact	Unique Name of the relationship.
ReferencedEntity	contact	The referenced table. The one in many-to-one.
ReferencedAttribute	contactid	The primary key of the referenced table.
ReferencingEntity	account	The table with a lookup column referencing the other table. The many in many-to-one.
ReferencingAttribute	primarycontactid	The name of the lookup column.
RelationshipType	OneToManyRelationship	A one-to-many relationship when viewed from the referenced (one) table. A many-to-one relationship when viewed from the referencing (many) table

Retrieve relationship information

If you use the XrmToolBox FetchXML Builder, you can see how this tool allows you to select the relationship to set the appropriate name, from, and to attribute values.

You can also use other tools and APIs to look up relationship data for the appropriate name, from, and to attribute values to use. Learn how to retrieve this data:

• Browse table definitions in your environment
• Query schema definitions

One-to-many relationships

Many-to-one and one-to-many relationships are like looking at two sides of a coin. The relationship exists between the tables, so the way you use it depends on which table is the base table for your query.

You can retrieve the same data as the previous example from the contact table using the same relationship, except from the side of the contact table. Use the data from the same Contact account_primary_contact one-to-many relationship, but adjust the values for the different view of the relationship.

<fetch top='5'>
   <entity name='contact'>
      <attribute name='fullname' />
      <link-entity name='account'
         from='primarycontactid'
         to='contactid'
         alias='account'>
         <attribute name='name' />
      </link-entity>
   </entity>
</fetch>

The following table shows the link-entity attribute values in this example:

Attribute	Value	Description
name	account	The logical name of the referencing table
from	primarycontactid	The name of the lookup column in the referencing account table
to	contactid	The primary key of the referenced contact table
alias	account	A value is recommended for the link-entity with a one-to-many relationship. If an alias isn't provided, a default alias is generated. In this example, if no alias is provided, the data is returned with a column named account1.name.
link-type	Not set	When no value is set, it defaults to inner

The results include the same records and data as the previous query using the many-to-one relationship, except the 'parent entity' is now contact instead of account.

 -----------------------------------------------------------------
 | fullname                   | account.name                     |
 -----------------------------------------------------------------
 | Susanna Stubberod (sample) | Litware, Inc. (sample)           |
 -----------------------------------------------------------------
 | Nancy Anderson (sample)    | Adventure Works (sample)         |
 -----------------------------------------------------------------
 | Maria Campbell (sample)    | Fabrikam, Inc. (sample)          |
 -----------------------------------------------------------------
 | Sidney Higa (sample)       | Blue Yonder Airlines (sample)    |
 -----------------------------------------------------------------
 | Scott Konersmann (sample)  | City Power & Light (sample)      |
 -----------------------------------------------------------------

Many-to-many relationships

Many-to-many relationships depend on an intersect table. An intersect table typically has just four columns, but only two of them are important. The two important columns match the primary key columns of the participating tables.

For example, the TeamMembership intersect table supports the teammembership_association many-to-many relationship between SystemUser and Team tables. It allows users to join multiple teams, and teams to have multiple users. TeamMembership has these columns: systemuserid, teamid.

If you want to retrieve information about users and the teams they belong to using the teammembership_association many-to-many relationship, you can use this fetchXML query:

<fetch top='2'>
   <entity name='systemuser'>
      <attribute name='fullname' />
      <link-entity name='teammembership'
         from='systemuserid'
         to='systemuserid'
         intersect='true' >
         <link-entity name='team'
            from='teamid'
            to='teamid'
            link-type='inner'
            alias='team'>
            <attribute name='name' />
         </link-entity>
      </link-entity>
   </entity>
</fetch>

There are two nested link-entities.

• The first one connects systemuser to the teammembership intersect table where systemuserid = systemuserid.
• The second one connects teammembership intersect table to team where teamid = teamid.

The results should look something like:

 --------------------------------------
 | fullname             | team.name   |
 --------------------------------------
 | FirstName LastName   | org26ed931d |
 --------------------------------------
 | # PpdfCDSClient      | org26ed931d |
 --------------------------------------

No relationship

It's possible to specify from and to attributes using columns that aren't part of a defined relationship.

For example, this query finds pairs of records where the Name column of an account record matches the FullName column of a contact record regardless of whether they reference each other in any of the lookup columns.

<fetch>
   <entity name='account'>
     <attribute name='name' />
     <link-entity name='contact'
       from='fullname'
       to='name'
       link-type='inner'
       alias='contact'>
       <attribute name='fullname' />
     </link-entity>
   </entity>
 </fetch>

Note

It is important that the columns specified in the from and to attributes are the same type even if they are not involved in a relationship. Using columns of different types will require a type conversion that may have performance impact and may fail for some column values.

The following column types can't be used in from and to attributes:

• File
• Image
• MultiSelect Field
• PartyList

Some columns can be used in from and to attributes but might result in poor performance:

• Columns of the Multiple Lines of Text type
• Columns of the Single Line of Text type with a maximum length larger than 850
• Formula columns
• Calculated columns
• Logical columns

Find records not in a set

You can use FetchXml to create a query to return records that aren't in a set using a left outer join. A left outer join returns each row that satisfies the join of the first input with the second input. It also returns any rows from the first input that had no matching rows in the second input. The non-matching rows in the second input are returned as null values.

You can perform a left outer join in FetchXML by using the entityname attribute in a condition element. The entityname attribute is valid in conditions, filters, and nested filters. Learn more about filters on link-entity.

For example, the following query returns all account records with no contacts.

<fetch>
   <entity name='account'>
      <attribute name='name' />
      <order attribute='name' />
      <link-entity name='contact'
         from='parentcustomerid'
         to='accountid'
         link-type='outer'
         alias='contact' />
      <filter type='and'>
         <condition entityname='contact'
            attribute='parentcustomerid'
            operator='null' />
      </filter>
   </entity>
</fetch>

Use advanced link types

The following link entity types don't directly correspond to T-SQL JOIN operator types and use subqueries instead. These types provides more advanced capabilities you can use to improve query performance and define more complex queries.

Name	Description
exists	A variant of inner that can provide performance benefits. Uses an EXISTS condition in the where clause. Use this when multiple copies of the parent row are not necessary in the results. Learn more about exists and in
in	A variant of inner that can provide performance benefits. Uses an IN condition in the where clause. Use this when multiple copies of the parent row are not necessary in the results. Learn more about exists and in
matchfirstrowusingcrossapply	A variant of inner that can provide performance benefits. Use this type when only a single example of a matching row from the linked entity is sufficient and multiple copies of the parent row in the results aren't necessary. Learn more about matchfirstrowusingcrossapply

Use exists or in link types

exists and in are variants of inner that use different conditions (EXISTS and IN respectively) in the where clause so that multiple copies of the parent row aren't returned in the results. Neither of these types return the column values of the link entity rows.

exists

These FetchXml and SQL examples show the patterns applied with exists.

FetchXml

<fetch>
   <entity name='contact'>
      <attribute name='fullname' />
      <link-entity name='account'
         from='primarycontactid'
         to='contactid'
         link-type='exists'>
         <filter type='and'>
            <condition attribute='statecode'
               operator='eq'
               value='1' />
         </filter>
      </link-entity>
   </entity>
</fetch>

SQL

select 
    "contact0".fullname as "fullname" 
from Contact as "contact0" 
where exists (
    select "account1".primarycontactid
    from Account as "account1"
    where "account1".statecode = 1
        and "contact0".contactid = "account1".primarycontactid)

in

These FetchXml and SQL examples show the patterns applied with in.

FetchXml

<fetch>
   <entity name='contact'>
      <attribute name='fullname' />
      <link-entity name='account'
         from='primarycontactid'
         to='contactid'
         link-type='in'>
         <filter type='and'>
            <condition attribute='statecode'
               operator='eq'
               value='1' />
         </filter>
      </link-entity>
   </entity>
</fetch>

SQL

select 
    "contact0".fullname as "fullname" 
from Contact as "contact0" 
where "contact0".contactid in (
    select "account1".primarycontactid
    from Account as "account1"
    where "account1".statecode = 1)

Using exists or in link types can reduce the size of intermediate or final query results, especially when many matching linked rows exist for the same parent rows, or when multiple link entities are used with the same parent Using exists or in link types can improve performance of the query compared to the inner type because it doesn't require returning a Cartesian product containing all possible permutations of rows from different linked entities for each parent row.

These link types might also allow Dataverse to only find the first matching linked entity row for each parent row, which is more efficient than finding all matching rows in the linked entity in an inner join.

Use matchfirstrowusingcrossapply link type

This link type produces a CROSS APPLY operator with a subquery using top 1 following this pattern:

FetchXml

<fetch>
   <entity name='contact'>
      <attribute name='fullname' />
      <link-entity name='account'
         from='primarycontactid'
         to='contactid'
         link-type='matchfirstrowusingcrossapply'>
         <attribute name='accountid' />
         <attribute name='name' />
      </link-entity>
   </entity>
</fetch>

SQL

select 
    "contact0".fullname as "fullname",
    "account1".accountid as "accountid",
    "account1".name as "name" 
from Contact as "contact0"
cross apply (
    select top 1
        "account1".accountid as "accountid",
        "account1".name as "name"
    from Account as "account1"
    where "contact0".contactid = "account1".primarycontactid
  ) "account1"

The matchfirstrowusingcrossapply link type is equivalent to the inner type except it only returns the parent row at most once. The parent row is returned only if there are matching rows in the linked entity but, unlike in and exists types, it does return column values from one of the matching rows in the linked entity. Use this when only a single example of a matching row from the linked entity is sufficient and multiple copies of the parent row in the results aren't necessary.

Related table property/attribute names are inconsistent

When using the matchfirstrowusingcrossapply link type, the names of the properties returned using Web API, or the SDK Entity.Attributes collection Keys values for the related table columns are different than other types of joins. Usually, these will follow the <tablealias>.<logicalname> format. However, for the matchfirstrowusingcrossapply link type, the SchemaName values are used without the table alias prefix.

Using the previous query example with any other link-type, you can expect the properties or keys to have these names:

• fullname
• contactid
• account1.accountid
• account1.name

But with the matchfirstrowusingcrossapply link type, the properties or keys have these names:

• fullname
• contactid
• AccountId
• Name

Next steps

Learn how to order rows.

Order rows