FetchXML Multiple Links

One common pattern of queries I see about FetchXML is how to write queries to ask two different questions about the same related entity. For example:

• Invoices that include product A AND product B?
• Contacts that have pending emails AND no sent emails?
• Visits that have a page view of the checkout page BUT NOT the “thank you” page?

Multiple Links

The key with these sorts of queries is that each question you want to ask of the related entity requires a separate link. Taking the first example:

Invoices that include product A AND product B?

If we write this as:

Advanced Find

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="invoice">
    <link-entity name="invoicedetail" to="invoiceid" from="invoiceid" alias="id" link-type="inner">
      <link-entity name="product" to="productid" from="productid" alias="p" link-type="inner">
        <all-attributes />
      </link-entity>
      <all-attributes />
    </link-entity>
    <all-attributes />
    <filter>
      <condition attribute="productnumber" entityname="p" operator="eq" value="A" />
      <condition attribute="productnumber" entityname="p" operator="eq" value="B" />
    </filter>
  </entity>
</fetch>

SELECT *
FROM   invoice AS i
       INNER JOIN
       invoicedetail AS id
       ON i.invoiceid = id.invoiceid
       INNER JOIN
       product AS p
       ON id.productid = p.productid
WHERE  p.productnumber = 'A'
       AND p.productnumber = 'B';

This is a common mistake I see, and one you can make easily if you try to build this query in Advanced Find. When the query is run, it tries to find invoices that have an invoice item that has a product that has the product number “A” and “B”. Since it’s impossible for a product to have two different product numbers in the same field, you’ll never get any results for this query.

The key change we need to make is to have two separate links to the invoicedetail entity, one filtered for product A and the other for product B:

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="invoice">
    <link-entity name="invoicedetail" to="invoiceid" from="invoiceid" alias="id_a" link-type="inner">
      <link-entity name="product" to="productid" from="productid" alias="p_a" link-type="inner">
        <filter>
          <condition attribute="productnumber" operator="eq" value="A" />
        </filter>
        <all-attributes />
      </link-entity>
      <all-attributes />
    </link-entity>
    <link-entity name="invoicedetail" to="invoiceid" from="invoiceid" alias="id_b" link-type="inner">
      <link-entity name="product" to="productid" from="productid" alias="p_b" link-type="inner">
        <filter>
          <condition attribute="productnumber" operator="eq" value="B" />
        </filter>
        <all-attributes />
      </link-entity>
      <all-attributes />
    </link-entity>
    <all-attributes />
  </entity>
</fetch>

SELECT *
FROM   invoice AS i
       INNER JOIN
       invoicedetail AS id_a
       ON i.invoiceid = id_a.invoiceid
       INNER JOIN
       product AS p_a
       ON id_a.productid = p_a.productid and p_a.productnumber = 'A'
       INNER JOIN
       invoicedetail AS id_b
       ON i.invoiceid = id_b.invoiceid
       INNER JOIN
       product AS p_b
       ON id_b.productid = p_b.productid and p_b.productnumber = 'B';

With this change the query will now find invoices that have an invoice item that has a product that has the product number “A”, and also an invoice item that has a product that has the product number “B”. It’s more long-winded to write but gets us to the right answer.

Unfortunately it’s not possible to build this query in Advanced Find, so if you need to use a query like this in your app you’ll need to get the query ready in FetchXML Builder and save it from there as a view or marketing list (but see the workaround at the end).

Combining with “Not In” Queries

Taking another example:

Visits that have a page view of the checkout page BUT NOT the “thank you” page?

If you’re using ClickDimensions you get a lot of powerful web tracking information recorded in custom entities. Two of these are Page View, which records an individual web page that someone has viewed, and Visit, which represents a session that can cover multiple pages. Each Page View is linked to a Visit. We can find abandoned shopping carts by querying these to find sessions which include a visit to the shopping cart page but not to the page that it shown after a purchase is complete. This makes use of the “not in” pattern of using a left outer join and null query.

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="cdi_visit">
    <link-entity name="cdi_pageview" to="cdi_visitid" from="cdi_visitid" alias="checkout" link-type="inner">
      <filter>
        <condition attribute="cdi_uri" operator="eq" value="https://example.com/checkout" />
      </filter>
      <all-attributes />
    </link-entity>
    <link-entity name="cdi_pageview" to="cdi_visitid" from="cdi_visitid" alias="finished" link-type="outer">
      <filter>
        <condition attribute="cdi_uri" operator="eq" value="https://example.com/checkout/thankyou" />
      </filter>
      <all-attributes />
    </link-entity>
    <all-attributes />
    <filter>
      <condition attribute="cdi_pageviewid" entityname="finished" operator="null" />
    </filter>
  </entity>
</fetch>

SELECT *
FROM   cdi_visit AS v
       INNER JOIN
       cdi_pageview AS checkout
       ON v.cdi_visitid = checkout.cdi_visitid
          AND checkout.cdi_uri = 'https://example.com/checkout'
       LEFT OUTER JOIN
       cdi_pageview AS finished
       ON v.cdi_visitid = finished.cdi_visitid
          AND finished.cdi_uri = 'https://example.com/checkout/thankyou'
WHERE  finished.cdi_pageviewid IS NULL;

“All” / “Only” Queries

There’s also a related type of query that checks if something is true for all related entities, e.g.:

• Accounts that only have contacts with an info@ email address?
• Users that have sales on all their accounts this month?

It’s actually possible to re-phrase these queries into the same pattern as above of asking two separate questions of the link entity. For example:

Accounts that only have contacts with an info@ email address?

You could re-write this as:

Accounts that have a contact with an info@ email address and don’t have any contacts that don’t have an info@ email address?

Now we can apply the same pattern again:

<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="account">
    <link-entity name="contact" to="accountid" from="parentcustomerid" alias="info" link-type="inner">
      <filter>
        <condition attribute="emailaddress1" operator="like" value="info@%" />
      </filter>
      <all-attributes />
    </link-entity>
    <link-entity name="contact" to="accountid" from="parentcustomerid" alias="noninfo" link-type="outer">
      <filter>
        <condition attribute="emailaddress1" operator="not-like" value="info@%" />
      </filter>
      <all-attributes />
    </link-entity>
    <all-attributes />
    <filter>
      <condition attribute="contactid" entityname="noninfo" operator="null" />
    </filter>
  </entity>
</fetch>

SELECT *
FROM   account AS a
       INNER JOIN
       contact AS info
       ON a.accountid = info.parentcustomerid
          AND info.emailaddress1 LIKE 'info@%'
       LEFT OUTER JOIN
       contact AS noninfo
       ON a.accountid = noninfo.parentcustomerid
          AND noninfo.emailaddress1 NOT LIKE 'info@%'
WHERE  noninfo.contactid IS NULL;

Advanced Find

I mentioned earlier that you couldn’t build these queries in Advanced Find, and that’s mostly true. If you try to create one of these queries exactly as I’ve shown you’ll get an error when you try to add the second instance of the related entity:

The relationship you are adding already exists in the query.

But with a bit of creativity we can switch the query around to something that Advanced Find can handle, although as a database person it’s not as “nice”.

Instead of having our main entity listed only once, we can join back to it from the related entity. Instead of joining from invoice to invoicedetail twice, we join from invoice to invoicedetail, back to invoice and on to invoicedetail again:

This works fine for this case, but you may not get the results you want if you try this in combination with a “not in” type query. That would also apply to all the other link entities you chain off it, and could have the effect of changing an “in” query to a “not in”.