Left Join with Not-in operator in FetchXml in Microsoft Dynamics CRM 2013

Just the other day, I was discussing with one of my colleagues about his experience of building fetch xml reports for one online implementations and we stumbled upon the topic where he needed to show the list of accounts with no cases associated to it. With SSRS reports it’s just a simple query. However for online, we have to limit our self with fetch xml reports and till 2011 version of CRM, it was not possible to construct a query of left join with NOT-IN operator.

But in dynamics 2013, you can construct the query to do this. The following is the query to retrieve the list of accounts with no leads. The sample code is taken from MSDN.

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

For full documentation refer to the link : http://msdn.microsoft.com/en-in/library/dn531006.aspx

Hope this helps!