title: Microsoft Dynamics AX Outer Join different than equivalent SQL
body: <p>I have the below x++ method:</p>
  
  <pre><code>public void run()
  {
      CustTable           ct;
      DirPartyTable       dir;
      DirAddressBookParty party;
      DirAddressBook      ab;
      int                 ctr = 0;
      ;  
  
       while
         select * from ct
           where ct.UT_AP_CustomerStatus == 1
         outer join * from dir
           where dir.RecId == ct.Party 
              &amp;&amp; dir.Partition == ct.Partition
         outer join * from party
           where ct.Party == party.Party
         outer join * from ab
           where party.AddressBook == ab.RecId
              &amp;&amp; ab.Name == ''
      {
          ctr++;
      }
  
      info(strFmt('Finished with %1 rows selected',ctr));
  }
  </code></pre>
  
  <p>When this runs, it ALWAYS returns 2,346 rows, which is how many rows are in CUSTTABLE.  However, when I run the below SQL in SSMS, it ONLY returns one row, like it should!</p>
  
  <pre><code>SELECT *
    FROM [DYNAMICS_AX].[dbo].[CUSTTABLE] ct
         LEFT OUTER JOIN [DYNAMICS_AX].[dbo].[DIRPARTYTABLE] dir
                      ON dir.RECID = ct.PARTY
                     AND dir.PARTITION = ct.PARTITION
         LEFT OUTER JOIN [DYNAMICS_AX].[dbo].[DIRADDRESSBOOKPARTY] party
                      ON ct.PARTY = party.PARTY
         LEFT OUTER JOIN [DYNAMICS_AX].[dbo].[DIRADDRESSBOOK] ab
                      ON party.ADDRESSBOOK = ab.RECID
   WHERE ct.UT_AP_CUSTOMERSTATUS = 1
     AND ab.NAME IS NULL
  </code></pre>
  
  <p>Can anyone see what's wrong with the x++ query vs. my SQL statement?</p>
  
  <p>Thanks,
  Craig</p>
  
  <p>With all of your great help/suggestions, I realized what needed to be changed.  First off, the reason my SQL was returning one row with the ab.NAME is null, is because the left joins that didn't find anything was putting NULLS into the result set.  That and showing me how to look up the SQL that x++ generates really helped as well, and I was able to change my x++ to below to give me the single row I was wanting to update:</p>
  
  <pre><code>while
       select ct
          where ct.UT_AP_CustomerStatus == 1
          notExists join party from party
          where ct.Party == party.Party
  </code></pre>
  
  <p>Thanks for your help!</p>