title: How to query sum total of transitively linked child transactions from database?
body: <p>I got this one assignment which has a lot of weird stuff to do. I need to create an API for storing transaction details and do some operations. One such operation involves retrieving a sum of all transactions that are transitively linked by their parent_id to $transaction_id.</p>
  
  <p>If A is the parent of B and C, and C is the parent of D and E, then </p>
  
  <blockquote>
    <p>sum(A) = A + B + C + D + E</p>
  </blockquote>
  
  <p>note: not just immediate child transactions.</p>
  
  <p>I have this sample data in the SQL database as given below.</p>
  
  <pre><code>MariaDB [test_db]&gt; SELECT * FROM transactions;
  +------+-------+----------+---------+
  | t_id | t_pid | t_amount | t_type  |
  +------+-------+----------+---------+
  |    1 |  NULL | 10000.00 | default |
  |    2 |  NULL | 25000.00 | cars    |
  |    3 |     1 | 30000.00 | bikes   |
  |    4 |  NULL | 10000.00 | bikes   |
  |    5 |     3 | 15000.00 | bikes   |
  +------+-------+----------+---------+
  5 rows in set (0.000 sec)
  
  MariaDB [test_db]&gt; 
  </code></pre>
  
  <p>where <strong>t_id</strong> is a unique <strong>transaction_id</strong> and <strong>t_pid</strong> is a <strong>parent_id</strong> which is either null or an existing t_id.</p>
  
  <p>so, when I say <strong>sum(t_amount) where t_id=1</strong>, I want the result to be </p>
  
  <pre><code>sum(1+3+5) -&gt; sum(10000 + 30000 + 15000) = 55000.
  </code></pre>
  
  <p>I know I can achieve this in a programmatic way with some recursion which will do repeated query operations and add the sum. But, that will give me poor performance if the data is very large say, millions of records.</p>
  
  <p>I want to know if there is any possibility of achieving this with a complex query. And if yes, then how to do it?</p>
  
  <p>I have very little knowledge and experience with databases. I tried with what I know and I couldn't do it. I tried searching for any similar queries available here and I didn't find any.
  With what I have researched, I guess I can achieve this with stored procedures and using the HAVING clause. Let me know if I am right there and help me do this.</p>
  
  <p>So, any sort of help will be appreciated.</p>
  
  <p>Thanks in advance.</p>