convert my sql query to queryexpression or fetchxm

Question

I have this SQL query where I am trying to fetch the opportunityId from opportunity entity for whom approvaldocument has not been created (approval document is the name of the other entity) .I dont think fetchxml supports such kind of query. I am new to crm and my project is in crm 4.0 version.

here's the sql query:

Select OpportunityId from opportunity AS c left JOIN (select a.opportunitynameid from opportunity o
JOIN ApprovalDocument a ON 
a.opportunitynameid=o.OpportunityId) AS b ON c.OpportunityId=b.opportunitynameid
Where b.opportunitynameid IS NULL and statecode=0

I converted this into a fetchxml, but that didn't give the correct result.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true"> 
    <entity name="opportunity"> <attribute name="opportunityid" /> 
       <link-entity name="approvaldocument" from="opportunitynameid" to="opportunityid" alias="a" link-type="outer"> <attribute name="opportunitynameid" /> 
       </link-entity> 
       <filter type="and"> 
          <condition entityname="a" attribute="opportunitynameid" operator="null" /> 
       </filter> 
    </entity> 
<fetch/>

Answer 1

Natively it is not possible to create an advanced find to query for the absence of a relationship. However there are several different solutions for achieving this functionality:

Workaround: Create a marketing list with the full set of records and then remove records using the inverse of your condition. The steps for doing this are nicely laid out in this article.

Modifying FetchXML and Third Party Solutions: Although the advanced find cannot show "Not In" results, the underlying FetchXML does support this functionality. An example of manually building such Fetch is shown here. Also there are several third party tools which leverage this ability to provide Not In functionality directly in the advanced find. The best solution I am aware of is available here.