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

For full documentation refer to the link :

Hope this helps!


About Debajit
I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under For consulting/ training, drop me a note at or visit our website

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

  1. Pingback: {How To} Create a view in Dynamics CRM that shows UNION of results | Debajit's Dynamic CRM Blog

  2. Pingback: Debajit's Dynamics CRM Blog

  3. Nathan says:

    We created a solution such that depending on how you setup your advanced find query, the query expression is modified to be a left outer join. This will allow the user to do “Not In” quires without having to deal with fetch xml.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: