Modifed Preorder Tree Transversal Child Roll Up (including grandchildren)

[Andrew K]

I'm trying to roll up spend by child company including all the grandchildren companies without any recursive functions.

My data set looks similar to this format:

Parent A
- Child A.1 - $1,000
- Child A.2 - $2,000
- - Grandchild A.2.1 - $500
- - Grandchild A.2.2 - $750
- Child A.3 - $3,000
- Child A.4 - $4,000
Parent B
- Child B.1 - $11,000
- Child B.2 - $12,000
- - Grandchild B.2.1 - $1,500
- - Grandchild B.2.2 - $1,750
- Child B.3 - $13,000
- Child B.4 - $14,000


What I'd like to do is sum by child for Parent A, so the result would output like:

Child A.1 - $1,000
Child A.2 - $3,250
Child A.3 - $3,000
Child A.4 - $4,000

This is a simplified structure of my companies table:
id
name
parent_id
lft
rght

This is a simplified structure of my spend table:
id
company_id
amount
date


I know how to list out each child and their amounts for just Parent A:
SELECT
`Company`.`name` AS `name`,
SUM(`Spend`.`amount`) AS `amount`
FROM
`spend_table` AS `Spend`
INNER JOIN companies_table AS `Company` ON `Spend`.`company_id` = `Company`.`id`
INNER JOIN companies_table AS `thisCompany` ON `Company`.`lft` BETWEEN `thisCompany`.`lft` AND `thisCompany`.`rght`
WHERE
`thisCompany`.`name` = 'Parent A'
GROUP BY
`Company`.`name`

Which would output:

Child A.1 - $1,000
Child A.2 - $2,000
Grandchild A.2.1 - $500
Grandchild A.2.2 - $750
Child A.3 - $3,000
Child A.4 - $4,000

And I know how to sum for each child (excluding grandchildren) for Parent A:
SELECT
`Company`.`name` AS `name`,
SUM(`Spend`.`amount`) AS `amount`
FROM
`spend_table` AS `Spend`
INNER JOIN companies_table AS `Company` ON `Spend`.`company_id` = `Company`.`id`
INNER JOIN companies_table AS `thisCompany` ON `Company`.`lft` BETWEEN `thisCompany`.`lft` AND `thisCompany`.`rght`
WHERE
`thisCompany`.`name` = 'Parent A'
`Company`.`parent_id` = `thisCompany`.`id`
GROUP BY
`Company`.`name`

Which would output:

Child A.1 - $1,000
Child A.2 - $2,000
Child A.3 - $3,000
Child A.4 - $4,000

Can someone help me? I believe I need a subselect, but am having difficulty figuring it out.

Thanks!



Edited 1 time(s). Last edit at 08/01/2011 12:41PM by Andrew K.