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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]