Use a left outer join in FetchXML to query for records "not in"

 

Applies To: Dynamics CRM 2013

You can use a left outer join in FetchXML to perform a query that filters on the join table, such as to find all contacts who did not have any campaign activities in the past two months. Another common use for this type of a query is to find records “not in” a set, such as in these cases:

• Find all leads that have no tasks

• Find all accounts that have no contacts

• Find all leads that have one or more tasks

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 nonmatching rows in the second input are returned as null values.

You can perform a left outer join in FetchXML by using the entityname attribute as a condition operator. The entityname attribute is valid in conditions, filters, and nested filters.

You can create a query using a left outer join programmatically and execute the query using the RetrieveMultipleRequest, and you can save the query by creating a SavedQuery record. You can open a saved query that contains a left outer join in the Advanced Find or Saved Query editors in the web application and execute and view results, but some editor functionality is disabled. Those editors will allow modifications to the query, such as to change the columns returned, but the editor does not support changing the left outer join.

Example: Find all accounts that have no leads

The following shows how to construct the query in FetchXML:

<fetch mapping='logical'>
  <entity name='account'>
    <attribute name='name'/>
    <link-entity name='lead' from='leadid' to='originatingleadid' link-type='outer'/>
    <filter operator='and'>
      <condition entityname='lead' attribute='leadid' operator='null'/>
    </filter>
  </entity>
</fetch>

Example: Find all leads that have no tasks, using an alias

The following shows how to construct the query in FetchXML:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="lead">
    <attribute name="fullname" />
    <link-entity name="task" from="regardingobjectid" to="leadid" alias="ab" link-type="outer">
       <attribute name="regardingobjectid" />
    </link-entity>
    <filter type="and">
        <condition entityname="ab" attribute="regardingobjectid" operator="null" />
    </filter>
  </entity>
<fetch/>

This is equivalent to the following SQL:

SELECT lead.FullName
FROM Leads as lead
LEFT OUTER JOIN Tasks as ab
ON (lead.leadId  =  ab.RegardingObjectId)
WHERE ab.RegardingObjectId is null

See Also

Build queries with FetchXML
Sample: Use aggregation in FetchXML
Use FetchXML to construct a query
Sample: Validate and execute a saved query