Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id B628F475C85
	for <pgsql-sql@postgresql.org>; Sun,  6 Oct 2002 02:15:42 -0400 (EDT)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id 55F53475B47
	for <pgsql-sql@postgresql.org>; Sun,  6 Oct 2002 02:15:42 -0400 (EDT)
Received: by news.hub.org (Postfix, from userid 8)
	id AB62838165E; Sun,  6 Oct 2002 02:15:41 -0400 (EDT)
From: gzscott2001@yahoo.ca (Scott Yaung)
X-Newsgroups: comp.databases.postgresql.sql
Subject: Get A Tree from a table
Date: 5 Oct 2002 22:37:05 -0700
Organization: http://groups.google.com/
Lines: 46
Message-ID: <341834e1.0210052137.7cbd6342@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Complaints-To: groups-abuse@google.com
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/83
X-Sequence-Number: 9801

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