Left outer joins in FetchXML with multiple conditions

Question

I'm trying to do a left outer join in FetchXML with multiple conditions.

Here is the approximate SQL of what I'm trying to achieve:

SELECT incident.*, externalCheck.*
FROM incident
LEFT OUTER JOIN externalCheck 
  ON externalCheck.incidentId = incident.incidentId
  AND externalCheck.checkType = 1
  AND externalCheck.isLatest = 1;

NB: This should always return a 1:1 relationship since our business logic requires that there is only one isLatest for each checkType.

And here is my FetchXML:

<entity name="incident">
  <all-attributes />
  <link-entity name="externalCheck" from="incidentId" to="incidentId" link-type="outer">
    <filter type="and">
      <condition attribute="checkType" operator="eq" value="1" />
      <condition attribute="isLatest" operator="eq" value="1" />
    </filter>
    <all-attributes />
  </link-entity>
</entity>

The Problem

The problem is that incident records where the right-hand side of the join are null (i.e. there is no externalCheck record) are not being returned, whereas in a left outer join I would expect that the incident record is still returned even if the right-hand side of the join is null.

What I suspect is that FetchXML is converting my filter to a WHERE clause, rather than adding the conditions to the ON clause.

The Question

Can anyone confirm what is happening, and a possible solution?

score 1 · Answer 1 · answered Mar 08 '18 at 03:57

1

Your suspicion is correct. But you can overcome from it somewhat.

Fetchxml is flexible & the below snippet will give the results for left outer join with multiple clause.

<entity name="incident">
  <all-attributes />
  <link-entity name="externalCheck" alias="ext" from="incidentId" to="incidentId" link-type="outer">
    <filter type="and">
      <condition attribute="checkType" operator="eq" value="1" />
      <condition attribute="isLatest" operator="eq" value="1" />
    </filter>
  </link-entity>
   <filter>
    <condition entityname="ext" attribute="externalCheckid" operator= "null" />
   </filter>
</entity>

The real problem is with externalCheck.*, you cannot get the related entity attributes. Have to remove the <all-attributes /> from link-entity node.

answered Mar 08 '18 at 03:57

Arun Vinoth-Precog Tech - MVP

22,364
14
59
168

Hi Arun. Thanks for your answer. I've tried various versions of your XML, but I keep getting an error that "entityname" is not a valid attribute for the condition. Without the entityname, the fetch will run, but it makes no difference to the number of rows returned. – Mark Micallef Mar 09 '18 at 00:30
Looks like it should work, but XrmToolBox is complaining about that attribute."The entityname attribute is not a valid attribute for link-entity." This despite the fact that I've added the attribute on the condition node. Weird. – Mark Micallef Mar 09 '18 at 01:03
@MarkMicallef forget the xrmtoolbox, is it executing in your code & fetch results ? – Arun Vinoth-Precog Tech - MVP Mar 13 '18 at 21:48

Left outer joins in FetchXML with multiple conditions

1 Answers1