Is it possible to make a recursive SQL query?

Question

I have a table similar to this:

CREATE TABLE example (   id integer primary key,   name char(200),   parentid integer,   value integer); 

I can use the parentid field to arrange data into a tree structure.

Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?

UPDATE: I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.

BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!

Answer 1

Here is an example script using common table expression:

with recursive sumthis(id, val) as (     select id, value     from example     where id = :selectedid     union all     select C.id, C.value     from sumthis P     inner join example C on P.id = C.parentid ) select sum(val) from sumthis 

The script above creates a 'virtual' table called sumthis that has columns id and val. It is defined as the result of two selects merged with union all.

First select gets the root (where id = :selectedid).

Second select follows the children of the previous results iteratively until there is nothing to return.

The end result can then be processed like a normal table. In this case the val column is summed.

Answer 2

Since version 8.4, PostgreSQL has recursive query support for common table expressions using the SQL standard WITH syntax.