There is a company table. It stores the following data: ID, company name, budget, ID for the parent company (parent ID), the total amount. It is necessary to calculate the budget of the company + budget subsidiaries. I do this in the total amount column.

 "SELECT o.id, o.title, o.budget, o.pid, (select sum(o1.budget)+o.budget from company o1 where o1.pid=o.id) total FROM company o ORDER BY o.id";

For this query, the amounts are displayed only one level lower, and I need to calculate the budgets of all the affiliated subsidiaries.

  • Unsuccessfully, you chose a DBMS, you need recursive queries that exist in almost all modern DBMS, except MySQL. You have a way only to the side of the stored procedure and possibly temporary tables. - Mike

1 answer 1

With your current structure (the Adjacency List ), you will have to make several queries in order to sum up the data.

If you do not switch to another DBMS, you can choose another way of representing the tree data: Nested Sets , Materialized Path or Closure Table .

See the review http://www.slideshare.net/quipo/trees-in-the-database-advanced-data-structures/