64 CHAPTER 4: NESTED SETS MODEL OF HIERARCHIES himself and all his subordinates. First, we need to have a table for the sales that each member made CREATE TABLE Sales (member CHAR(10) NOT NULL PRIMARY KEY, sale_amt DECIMAL(12,4) NOT NULL); SELECT :my_member, SUM(S1.sale_amt) AS total_sales FROM OrgChart AS Mgrs, OrgChart AS Workers, Sales AS S1 WHERE Workers.lft BETWEEN Mgrs.lft AND Mgrs.rgt AND P1.job_title = Workers.job_title AND Mgrs.member = :my_member; A slightly trickier function involves using quantity columns in the nodes to compute an accumulated total. This usually occurs in parts explosions, where one assembly may contain several occurrences of subassemblies. Let's assume we have a table called "Blueprint" with the price and quantity for each part required for each subassembly; for example, an assembly might require 10 Number 5 machine screws at $0.07 each. The total cost of any given part would be: SELECT :this_part, SUM(Subassem.qty * Subassem.price) AS totalcost FROM Blueprint AS Assembly, Blueprint AS Subassem WHERE Subassem.lft BETWEEN Assembly.lft AND Assembly.rgt AND Assembly.part = :this_part; The use of AVG(), MIN(), and MAX() aggregate functions is possible, but you have to watch out for the meaning of the results in the context of your data model. 4.5 Deleting Nodes and Subtrees Another interesting property of the nested sets model is that subtrees must fill from lft to rgt. In other tree representations, it is possible for a parent node to have a right child and no left child, but this can make traversals more complicated in exchange for being able to assign significance to the position of a node within a group of siblings.