title: convert SQL Query with Join to X++ Dynamics AX scripting
body: <p>I have a SQL query that is not that simple as it contains Join</p>
  
  <pre><code>SELECT *
   FROM table1 a inner join table2 b on a.field1 = b.field1
   inner join table3 c on a.field2 = c.field2 
   inner join table4 d on c.field3 = d.field3
    inner join table5 e on c.field4 = e.field4
   WHERE a.location = 'asia' AND  b.modType = 1
   and c.discount = 'sample'
   and d.name =  'hello' 
  and e.name in ('one', 'two', 'three')
  </code></pre>
  
  <p>I like to convert it to X++ scripting, here's what I did</p>
  
  <pre><code>  while select forUpdate 
          table1  join table2 where table1.field1 == table2.field1 
          join table3 where table1.field2 == table3.field2
          join table4 where table3.field3 == table4.field3
          join table5 where table3.category == table5.recid
  
          &amp;&amp; table1.location  == 'asia' &amp;&amp; table2.modtye == 2
          &amp;&amp; table3.discount == 'sample' 
          &amp;&amp; table4.name ==  'hello' 
         &amp;&amp; table5.name in  ('one', 'two', 'three')
  </code></pre>
  
  <p>I need to retrieve the data to update</p>
  
  <p>But the x++ is wrong and unsuccessful.</p>
  
  <p>Hope to get expert advice on this as I am new to X++ scipting</p>