MySQL Forums :: General :: Modifed Preorder Tree Transversal Child Roll Up (including grandchildren)


Advanced Search

Modifed Preorder Tree Transversal Child Roll Up (including grandchildren)
Posted by: Andrew K ()
Date: August 01, 2011 12:32PM

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.

Options: ReplyQuote


Subject Written By Posted
Modifed Preorder Tree Transversal Child Roll Up (including grandchildren) Andrew K 08/01/2011 12:32PM
Re: Modifed Preorder Tree Transversal Child Roll Up (including grandchildren) Rick James 08/07/2011 11:10PM
Re: Modifed Preorder Tree Transversal Child Roll Up (including grandchildren) Andrew K 08/08/2011 07:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.