How do I build FetchXML to give me all members of list A outer joined with all members of list B by emailaddress1

Question

And is that even possible?

I have the fetch xml already to filter list B out of list A by record id however I also want the option to filter it by the email address.

For example:

List A has 2 records. Record 1: Bob@BobMail.com and Record 2: Susy@SusyMail.com. List B has 1 record. Record 3 Bob@BobMail.com which is a different contact than Record 1 but has the same email address.

Still with me? Sorry for the confusing explanation...

The query should take List A, do an outer join with List B to produce the result which only contains Record 2 with the email address Susy@SusyMail.com.

If there's any way I can be more clear on what I'm trying to do don't hesitate to ask.

Sounds to me like you want to do the equivalent of a T-SQL EXCEPT. Unfortunately this is not possible in a fetchxml query. However, you may be able to use the c# except extension method on two fetchxml query results to achieve the same results... — Ollie, Mar 05 '14 at 15:28
The logic works at the ID level. If Record 1 and Record 3 (Bob@BobMail.com) were the same record, it would correctly exclude him. I'm trying to do the same with e-mail. I hope I'm not completely out of luck... — Justin williams, Mar 05 '14 at 15:41

Justin williams · Accepted Answer · 2014-03-05T18:54:54.540

After a night to think about it and a fresh pair of eyes in the morning I realized I simply needed to link the listmember to the contact exactly as I had been with the by ID option and then add an additional link to the contact entity on the email address.

<fetch mapping="logical" version="1.0" page="1" count="100" >
<entity name="contact" >
    <link-entity name="listmember" from="entityid" to="contactid" >
        <filter>
            <condition attribute="listid" operator="eq" value="06197bff-a299-e311-aae4-6c3be5a892e8" />
        </filter>
    </link-entity>
    <attribute name="contactid" />
    <attribute name="emailaddress1" />
    <filter type="and" >
        <condition attribute="emailaddress1" operator="not-null" />
        <condition attribute="donotbulkemail" operator="ne" value="1" />
    </filter>
    <link-entity name="listmember" from="entityid" to="contactid" link-type="outer" alias="exclusionlist" >
        <attribute name="entityid" />
        <filter type="and" >
            <condition attribute="listid" operator="eq" value="06197bff-a299-e311-aae4-6c3be5a892e8" />
        </filter>
    </link-entity>
    <link-entity name="contact" from="emailaddress1" to="emailaddress1" link-type="outer" alias="emailaddress" >
        <attribute name="fullname" />
        <attribute name="emailaddress1" />
    </link-entity>
    <order descending="false" attribute="emailaddress1" />
</entity>

And then we filter out the reults by the alias:'

        results = results.Where(x => !x.Contains("exclusionlist.entityid") || !x.Contains("emailaddress.emailaddress1")).ToList();

Hopefully this helps someone else though I do feel a bit dumb for asking now...

How do I build FetchXML to give me all members of list A outer joined with all members of list B by emailaddress1

1 Answers1