Is it possible to make a recursive SQL query?

Question

I have a table like this:

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

I can use the parentid field to organize the data in a tree structure.

Now here is a bit that I cannot solve. Given the parent, is it possible to write an SQL statement to compile all value fields under this parent and overwrite the tree branch?

UPDATE: I am using posgreSQL, so the fancy MS-SQL features are not available to me. In any case, I would like this to be seen as a general SQL question.

By the way, I am very impressed with the fact that I have 6 answers in 15 minutes with a question! Go to stack overflow!

Answer 1

There are several ways in PostgreSQL to do what you need.

• If you can install the modules, look at the tablefunc tab. It has a connectby () function that handles the movement of trees. http://www.postgresql.org/docs/8.3/interactive/tablefunc.html

• Also check out the ltree contrib tab, which you can adapt to use your spreadsheet: http://www.postgresql.org/docs/8.3/interactive/ltree.html

• Or you can traverse the tree yourself using the PL / PGSQL function.

Something like that:

 create or replace function example_subtree (integer) returns setof example as 'declare results record; child record; begin select into results * from example where parent_id = $1; if found then return next results; for child in select id from example where parent_id = $1 loop for temp in select * from example_subtree(child.id) loop return next temp; end loop; end loop; end if; return null; end;' language 'plpgsql'; select sum(value) as value_sum from example_subtree(1234); 

Answer 2

Here is an example script using a 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 , which has the id and val columns. It is defined as the result of two selected unions with union all .

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

The second select iteratively follows the children of the previous results, while there is nothing to return.

The final result can be processed as a regular table. In this case, the val columns are summed.

Answer 3

Starting with version 8.4, PostgreSQL has recursive query support for common table expressions using SQL WITH syntax.

Answer 4

If you need a portable solution that will work on any ANSI SQL-92 RDBMS, you will need to add a new column to your table.

Joe Celko is the original author of the Nested Sets approach for storing hierarchies in SQL. You can google the “nested sets” hierarchy to learn more about this background.

Or you can simply rename parentid to leftid and add the truth .

Here is my attempt to summarize nested sets, which will be very scarce, because I am not Joe Tselko: SQL is a set-based language, and the adjacency model (storing the parent identifier) ​​is NOT a set-based hierarchy representation. Therefore, there is no pure set-based method to query an adjacency scheme.

However , most major platforms in recent years have implemented extensions to solve this exact problem. Therefore, if someone responds with a Postgres-specific solution, use it by all means.

Answer 5

The standard way to make a recursive query in SQL recursive CTE . PostgreSQL supports them since 8.4 .

In earlier versions, you can write a recursive set function:

 CREATE FUNCTION fn_hierarchy (parent INT) RETURNS SETOF example AS $$ SELECT example FROM example WHERE id = $1 UNION ALL SELECT fn_hierarchy(id) FROM example WHERE parentid = $1 $$ LANGUAGE 'sql'; SELECT * FROM fn_hierarchy(1) 

See article:

• Hierarchical queries in PostgreSQL

Answer 6

use a common table expression .

You can specify that this is only SQL Server 2005 or higher. Dale Ragan

here is an article about SqlTeam recursion without common table expressions.

Answer 7

If you are using SQL Server 2005, there is a really cool way to do this using Common Table Expressions.

Due to the creation of a temporary table, everything is required due to the work of gruntwork and, basically, allows you to do all this only with WITH and UNION.

Here is a good tutorial:

http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci1278207,00.html

Answer 8

The following code is compiled and verified OK.

 create or replace function subtree (bigint)
 returns setof example as $$
 declare
     results record;
     entry record;
     recs record;
 begin
     select into results * from example where parent = $ 1;
     if found then
         for entry in select child from example where parent = $ 1 and child parent loop
             for recs in select * from subtree (entry.child) loop
                 return next recs;
             end loop;
         end loop;
     end if;
     return next results;
 end;
 $$ language 'plpgsql';

The condition "child <> parent" is necessary in my case, because the nodes point to themselves.

Good luck :)

Answer 9

Oracle has "START WITH" and "CONNECT"

 select lpad(' ',2*(level-1)) || to_char(child) s from test_connect_by start with parent is null connect by prior child = parent; 

http://www.adp-gmbh.ch/ora/sql/connect_by.html

Answer 10

As a brief passage, although the question was answered very well, it should be noted that if we consider this as:

general SQL question

then the SQL implementation is fairly straightforward, since SQL'99 allows linear recursion in the specification (although I believe that RDBMSs does not fully implement the standard) using the WITH RECURSIVE statement. Therefore, from a theoretical point of view, we can do it right now.

Answer 11

None of the examples worked fine for me, so I fixed it as follows:

 declare
     results record;
     entry record;
     recs record;
 begin
     for results in select * from project where pid = $ 1 loop
         return next results;
         for recs in select * from project_subtree (results.id) loop
             return next recs;
         end loop;
     end loop;
     return
 end;

Answer 12

is SQL Server? Could you write a TSQL stored procedure that goes through and combines the results together?

I am also wondering if there is a way to execute SQL-only. Of the bits that I remember from my class of geographic databases should be.

Answer 13

I think SQL 2008 is easier in HierarchyID

Answer 14

If you need to store arbitrary graphs, not just hierarchies, you can push Postgres to the side and try a graph database such as AllegroGraph

Everything in the graph database is stored as a triple (source node, edge, target node) and gives you first class support for managing the graph and query structure using the SQL language.

It does not integrate well with something like Hibernate or Django ORM, but if you are serious about graph structures (this gives you more than just hierarchies such as a nested set model).

I also believe that Oracle has finally added real-time graphics support to its latest products, but I am amazed that it took so long, many problems can benefit from this model.