title: Nested notExists joins X++ (Dynamics AX3.0)
body: <p>When the following code executes:</p>
  
  <pre><code>   select sum(qty) from inventTrans
          index hint TransTypeIdx
          where inventTrans.ItemId        == itemId
             &amp;&amp; inventTrans.TransType     == InventTransType::Sales
             &amp;&amp; inventTrans.InventDimId   == inventDimId
          notExists join custTable
          where custTable.AccountNum      == inventTrans.CustVendAC
             &amp;&amp; custTable.CustGroup       == custGroupId
          notExists join salesTable
          where salesTable.SalesId        == inventTrans.TransRefId
             &amp;&amp; salesTable.Extraordinary  == NoYes::Yes;
  </code></pre>
  
  <p>The sql generated nests the second notExists join (salesTable) into the where clause of the first notExists join (custTable). ie</p>
  
  <pre><code>SELECT SUM(A.QTY)
  FROM
      INVENTTRANS A
  WHERE
      A.DATAAREAID  = 'MyCompany'
  AND A.ITEMID      = 'MyItem'
  AND A.TRANSTYPE   = 0
  AND A.INVENTDIMID = 'SomeValue'
  AND NOT EXISTS (SELECT 'x'
                  FROM CUSTTABLE C
                  WHERE C.DATAAREAID  ='MyCompany'
                  AND C.ACCOUNTNUM    =A.CUSTVENDAC
                  AND C.CUSTGROUP     ='SomeCustGroup'
                  AND NOT EXISTS (SELECT 'x'
                                  FROM SALESTABLE B
                                  WHERE
                                      B.DATAAREAID    ='MyCompany'
                                  AND B.SALESID       =A.TRANSREFID
                                  AND B.EXTRAORDINARY =1))
  </code></pre>
  
  <p>Is there any way to write the X++ select query to notExists join the salesTable to the inventTrans table instead of the custTable, so the SQL generated would be similar to the following?</p>
  
  <pre><code>SELECT SUM(A.QTY)
  FROM
      INVENTTRANS A
  WHERE A.DATAAREAID     = 'MyCompany'
  AND A.ITEMID           = 'MyItem'
  AND A.TRANSTYPE        = 0
  AND A.INVENTDIMID      = 'SomeValue'
  AND NOT EXISTS (SELECT 'x'
                  FROM CUSTTABLE C
                  WHERE C.DATAAREAID  ='MyCompany'
                  AND C.ACCOUNTNUM    =A.CUSTVENDAC
                  AND C.CUSTGROUP     ='SomeCustGroup')
  AND NOT EXISTS (SELECT 'x'
                  FROM SALESTABLE B
                  WHERE
                      B.DATAAREAID='MyCompany'
                  AND B.SALESID=A.TRANSREFID
                  AND B.EXTRAORDINARY=1)
  </code></pre>