Thread: Get A Tree from a table

I like to do something like this:(build a tree from relationship)
I have a table like this 

NODES__________________________________ParentID    Varchar(20)ParentType  varchar(20)ChildID     varchar(20)ChildType
varchar(20)

__________________________________
What in the table'A1', 'A', 'B1', 'B''A2', 'A', 'B2', 'B''A1', 'A', 'B3', 'B''B1', 'B', 'C1', 'C''B1', 'B', 'C2',
'C''C1','C', 'D1', 'D''A1', 'A', 'B4', 'B' 'B1', 'B', 'C5', 'C'
 

----------------------------------

now I like to get all nodes with 'A1' as root
to get the result like this'A1', 'A', 'B1', 'B''B1', 'B', 'C1', 'C''C1', 'C', 'D1', 'D''B1', 'B', 'C2', 'C''B1', 'B',
'C5','C''A1', 'A', 'B3', 'B''A1', 'A', 'B4', 'B' 
 

or I just like to get another tree start with 'B1'
like this'B1', 'B', 'C1', 'C''C1', 'C', 'D1', 'D''B1', 'B', 'C2', 'C''B1', 'B', 'C5', 'C'


How can i make it by sql , and sql functions
Thanks lot and regards.

Scott

Scott,

> NODES__________________________________
>  ParentID    Varchar(20)
>  ParentType  varchar(20)
>  ChildID     varchar(20)
>  ChildType   varchar(20)

This looks difficult... mainly becuase, in your shema, a "parent" node
can be its own child or grandchild, resulting in an endless loop.

If that was not your intention, you may wish to think of using a
different structure.

There are two main ways for you to approach this problem in 7.2.x:
1) Buy Joe Celko's "SQL for Smarties" and read up on pure-SQL tree
implementations, which he covers in far more depth than I want to here;
2) Explore the tree module in /contrib in your Postgresql source.

I understand from Joe Conway that in 7.3, you will have another choice,
as Postgres will offer support for Oracle's tree-like "IS CONNECTED BY"
expression.

Good luck!

-Josh Berkus

P.S. also, there's some articles on tree structures up at techdocs:http://techdocs.postgresql.org/

Scott Yaung wrote:
> I like to do something like this:(build a tree from relationship)
[snip]
> How can i make it by sql , and sql functions
> Thanks lot and regards.

There have been quite a few discussions of this topic in the past, so I would 
suggest you search through the archives.

In 7.3 (currently in beta) you can use contrib/ltree or contrib/tablefunc. 
Here's an example of using the connectby() function from contrib/tablefunc:

CREATE TABLE nodes(parentid varchar(20), parenttype varchar(20), childid 
varchar(20), childtype   varchar(20));
INSERT INTO nodes values('A1', 'A', 'B1', 'B');
INSERT INTO nodes values('A2', 'A', 'B2', 'B');
INSERT INTO nodes values('A1', 'A', 'B3', 'B');
INSERT INTO nodes values('B1', 'B', 'C1', 'C');
INSERT INTO nodes values('B1', 'B', 'C2', 'C');
INSERT INTO nodes values('C1', 'C', 'D1', 'D');
INSERT INTO nodes values('A1', 'A', 'B4', 'B');
INSERT INTO nodes values('B1', 'B', 'C5', 'C');

test=# SELECT * FROM connectby('nodes','childid','parentid','A1',0,'~') AS 
t(childid varchar, parentid varchar, level int, branch text); childid | parentid | level |   branch
---------+----------+-------+------------- A1      |          |     0 | A1 B1      | A1       |     1 | A1~B1 C1      |
B1      |     2 | A1~B1~C1 D1      | C1       |     3 | A1~B1~C1~D1 C2      | B1       |     2 | A1~B1~C2 C5      | B1
    |     2 | A1~B1~C5 B3      | A1       |     1 | A1~B3 B4      | A1       |     1 | A1~B4
 
(8 rows)

test=# SELECT * FROM connectby('nodes','childid','parentid','B1',0,'~') AS 
t(childid varchar, parentid varchar, level int, branch text); childid | parentid | level |  branch
---------+----------+-------+---------- B1      |          |     0 | B1 C1      | B1       |     1 | B1~C1 D1      | C1
     |     2 | B1~C1~D1 C2      | B1       |     1 | B1~C2 C5      | B1       |     1 | B1~C5
 
(5 rows)

HTH,

Joe