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?

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

Read more