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 that you want to return with your query. Use the following attributes:

Basic example

The following query returns up to five 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 it takes to execute the query. This limit protects performance. If you add more than 15 link-entity elements to a query, you get this error message:

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 basic example shows a many-to-one relationship where many account records can refer to one contact record. You define this information in the Account account_primary_contact many-to-one relationship, which has the following values:

Retrieve relationship information

If you use tools like XrmToolBox FetchXML Builder or Power Platform ToolBox FetchXML Studio, you can see how these tools allow 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 learn how to retrieve this data, see:

• Browse table definitions in your environment
• Query schema definitions

One-to-many relationships

Many-to-one and one-to-many relationships are like two sides of the same 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, but 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:

The results include the same records and data as the previous query that uses the many-to-one relationship. The difference is that 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 rely on an intersect table. An intersect table typically has just four columns, but only two of them are important. The two important columns correspond to 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. By using this relationship, users can join multiple teams, and teams can have multiple users. TeamMembership has these columns: systemuserid and teamid.

To retrieve information about users and the teams they belong to by using the teammembership_association many-to-many relationship, 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 look like:

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

No relationship

You can specify from and to attributes by 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>

You can't use the following column types in from and to attributes:

• File
• Image
• MultiSelect Field
• PartyList

You can use these column types in from and to attributes, but they 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 that returns records that aren't in a set by 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 have 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 provide more advanced capabilities you can use to improve query performance and define more complex queries.

Use exists or in link types

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

exists

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

<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>

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.

<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>

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. This reduction is especially helpful when many matching linked rows exist for the same parent rows, or when multiple link entities are used with the same parent. By using exists or in link types, you can improve the performance of the query compared to the inner link type because the query doesn't need to return a Cartesian product containing all possible permutations of rows from different linked entities for each parent row.

By using these link types, Dataverse might only need to find the first matching linked entity row for each parent row. This approach 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:

<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>

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 type when only a single example of a matching row from the linked entity is sufficient and results don't need multiple copies of the parent row.

Related table property and attribute names are inconsistent

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

By 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 by using the matchfirstrowusingcrossapply link type, the properties or keys have these names:

• fullname
• contactid
• AccountId
• Name

Next steps

Learn how to order rows.