Left Outer Joins in Dynamics CRM 2013

Roshan Mehta, 31 October 2013

We have had many requests from clients asking for the ability to show “empty sets” in reports and custom applications. For example, retrieving a list of Accounts with no Contacts, or a list of Contacts that have no Tasks. To achieve this, we use the “outer” link type in FetchXML queries and specify a null value in our condition expression.

The following example shows how we can retrieve a list of Accounts in CRM that have no associated Contacts via the parentcustomerid relationship. This FetchXML query can be run inside a custom report, or by executing a RetrieveMultipleRequest with a FetchExpression.

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