title: Multi Level Marketing Select and Insert Commision in one Query MS SQL
body: <p><code>--'------------------------------------------
  --' TREE 1
  --'------------------------------------------
  --'                 Company
  --'             --------|-------
  --'             |              |
  --'            A               B
  --'       -----|-----      -----|--------
  --'       |          |     |            |
  --'      AA         AB     BA          BB
  --'                      ---|---     ---|---
  --'                     |       |    |      |
  --'                    BBA    BBB   BBC    BBD
  --'------------------------------------------</code><br>
  Tree 1 is my multi level marketing tree</p>
  
  <p><a href="https://i.stack.imgur.com/EZqtG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EZqtG.png" alt="enter image description here"></a>
  <strong>Example :</strong> if BBA get the sales Commission $100, BBA get 60% $60, and BA get 35% $35, B get $5 and company get $10 for the sales commission.  </p>
  
  <p><strong>level 5 most the lowest level</strong></p>
  
  <p>when a sales success, will insert to tbl_trans, and commission will be calculate end the month, read the tbl_trans and calculate the sales commission divide to all referral and store in tbl_comm.</p>
  
  <p>one transaction at tbl_trans will according tbl_user to create 4 commissions and store in tbl_comm.</p>
  
  <p>this is sample data and table, please help me <a href="http://sqlfiddle.com/#!18/8585c" rel="nofollow noreferrer">http://sqlfiddle.com/#!18/8585c</a></p>
  
  <p>this is what i had tried, but dont have any idea to continue</p>
  
  <pre><code>WITH CTE 
  AS(
  SELECT * FROM tbl_user WHERE User_ID = 4
  UNION ALL
  SELECT B.*  FROM tbl_user B 
  INNER JOIN CTE ON B.User_ID=CTE.User_UplineID)
  SELECT * FROM CTE
  </code></pre>