title: Need hierarichal data from 3 tables in SQL Server
body: <p>I have following tables:</p>
  
  <p><strong>UserMaster</strong>:</p>
  
  <p><code>UserId Int, UserName Varchar(200),AddedBy Int</code></p>
  
  <pre><code>UserId     EmpName           AddedBy
  1          admin             0
  2          SubAdmin1         1
  3          SubAdmin2         1    
  4          Vikas             2      
  5          Mohit             4
  6          Atul              5 
  7          Vishal            6
  8          Mani              3
  9          Sunny             8
  </code></pre>
  
  <p><strong>SalesMaster</strong>:</p>
  
  <p><code>SalesId Int, UserId Int (FK_UserMaster_UserId) , Price Int</code>    </p>
  
  <pre><code>SalesId  UserId  Price
  
  1        1       100         
  2        2       200      
  3        3       300      
  4        4       500     
  5        5       100
  6        6       200
  7        7       111
  8        8       222
  9        9       333
  </code></pre>
  
  <p>Case 1: Now I want the price total of all the users who are under the one particular user and its own price also.
  Means If i consider UserId=1 , Then the price will be calculated for all users where Column value in AddedBy=1 
  and their lower level employees.
  Means the total Price of users will be calulated for the users having UserId are: 1,2,3,4,5,6,7,8,9.</p>
  
  <p>Case 2: Similarly, If i want to calculate the total price under UserId=3(SubAdmin2) then the total price from the salesMaster will be calculated for the Users having UserId are: 3,8,9</p>
  
  <p>The Result of first Case should be:</p>
  
  <pre><code>UserId     Price
  1          2066
  </code></pre>
  
  <p>The Result of Second Case should be:</p>
  
  <pre><code>UserId     Price
  3          300+222+333
  </code></pre>
  
  <p>Please Help</p>
  
  <p>Thanks &amp; Regards
  Nitin</p>