CREATE TABLE #companies (
	id int
	, name varchar(50)
)

CREATE TABLE #company_groups (
	company_id int
	, group_id int
	, ranking int
)

CREATE TABLE #groups (
	id int
	, parent_id int
	, name varchar(50)
)


INSERT INTO #companies VALUES (1, 'Company X')
INSERT INTO #companies VALUES (2, 'Company Y')

INSERT INTO #groups VALUES (1, NULL, 'Group A')
INSERT INTO #groups VALUES (2, 1, 'Group A-1')
INSERT INTO #groups VALUES (3, 1, 'Group A-2')
INSERT INTO #groups VALUES (4, 2, 'Group A-1-1')
INSERT INTO #groups VALUES (5, NULL, 'Group B')
INSERT INTO #groups VALUES (6, NULL, 'Group C')
INSERT INTO #groups VALUES (7, 6, 'Group C-1')
INSERT INTO #groups VALUES (8, 7, 'Group C-1-1')

INSERT INTO #company_groups VALUES (1, 1, 60)
INSERT INTO #company_groups VALUES (1, 4, 40)
INSERT INTO #company_groups VALUES (2, 4, 10)
INSERT INTO #company_groups VALUES (2, 5, 80)
INSERT INTO #company_groups VALUES (2, 7, 90)
INSERT INTO #company_groups VALUES (2, 8, 30)

/*
EXAMPLE OF DESIRED OUTPUT

In this case we're selecting all the top level groups 
(groups.parent_id IS NULL).

cumulative_ranking is the ranking for that company_group
and sum of all the rankings for the groups children
--------------------------------------------------
| company_name | group_name | cumulative_ranking |
--------------------------------------------------
| Company X	   | Group A    | 100                |
| Company X	   | Group B    | 0                  |
| Company X	   | Group C    | 0                  |
| Company Y    | Group A    | 10                 |
| Company Y    | Group B    | 80                 |
| Company Y    | Group C    | 120                |
--------------------------------------------------

Notes:
* Children in the groups table can go to any depth
* The eventual solution will be queried using a 
	specific group_id and company_id.
*/

DROP TABLE #groups
DROP TABLE #company_groups
DROP TABLE #companies