Re: Trees in SQL

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 4 Jul 2002 11:41:07 -0700
Message-ID: <c0d87ec0.0207041041.3954de35_at_posting.google.com>


>> The fun part is that the sum() function yields null if any summand
is
null, so if you're summing child nodes to parents, you only get a non-null value if all the child nodes are non-null. <<

Nope: SUM() and all other aggregate functions drop the NULLs first, then do their math; the exception is COUNT(*), which is a set function and not really an aggregate function.

The nice part of the nested set model is that you use a GROUP BY over the self-join, as for example:

SELECT O2.emp, SUM(S1.salary)

   FROM OrgChart AS O1, OrgChart AS O2,

        Salaries AS S1
  WHERE O1.lft BETWEEN O2.lft AND O2.rgt     AND O1.emp = S1.emp
  GROUP BY O2.emp;

It sounds like you would use something like

 SELECT T2.upc, SUM(price * discount) ... Received on Thu Jul 04 2002 - 20:41:07 CEST

Original text of this message