Aggregating over nodes in hierarchical trees

[McGehee, Robert]

Hello,
I have data in a hierarchical tree structure in one table (tree_tbl) containing the number of students enrolled in a college course. The college course name is stored as a tree giving the college name, department, and course name, and perhaps other descendent nodes such as section name. I have another table (node_tbl) that contains the particular nodes I am interested in such as the name of the college, department or course. I'd like to join the two tables so I can aggregate the total students in all courses containing the node names in the node_tbl. Unfortunately, I've been unable to solve this problem and was hoping for help. I had tried using the ltree data type for this purpose, but am open to other suggestions if a better data type exists.

Here is an example of what I'm trying to do:

CREATE TABLE tree_tbl (course LTREE, students INTEGER);
INSERT INTO tree_tbl VALUES ('Arts.English.Shakespeare', 10);
INSERT INTO tree_tbl VALUES ('Arts.Music.Composition', 15);
INSERT INTO tree_tbl VALUES ('Arts.Music.Theory', 11);
INSERT INTO tree_tbl VALUES ('Science.Math.Algebra', 21);
INSERT INTO tree_tbl VALUES ('Science.Biology.IntroBio.SectionA', 20);
INSERT INTO tree_tbl VALUES ('Science.Biology.IntroBio.SectionB', 30);
INSERT INTO tree_tbl VALUES ('Science.Biology.Genetics', 3);

CREATE TABLE node_tbl (node TEXT);
INSERT INTO node_tbl VALUES ('Arts');
INSERT INTO node_tbl VALUES ('English');
INSERT INTO node_tbl VALUES ('Biology');

I would then like to join these two tables so that I can sum the total number of students in each area. Something like this:

SELECT n.node, sum(students) as students
FROM tree_tbl t, node_tbl n
WHERE t.course ~ '.*' || n.node || '.*'
GROUP BY n.node;

My hope that this query would return this:
node students
------- --------
Arts 36
English 10
Biology 53

Unfortunately, this query gives the error:
ERROR: syntax error at position 0
LINE 3: WHERE t.course ~ '.*' || n.node || '.*'
^

Any help with this problem would be greatly appreciated.

Thanks,
Robert McGehee

[h***@public.gmane.org]

Post by McGehee, Robert
SELECT n.node, sum(students) as students
FROM tree_tbl t, node_tbl n
WHERE t.course ~ '.*' || n.node || '.*'
GROUP BY n.node;

I'd write this as

SELECT n.node, sum(students) AS students
FROM tree_tbl t
JOIN node_tbl n ON t.course ~ ('*.' || n.node || '.*')::lquery
GROUP BY n.node;