Thread: Hierarchical queries
Hello all, how do you usually handle hierarchical (tree-like) queries? I'm sure someone else needed this already. Thanks in advance, Paulo -- Paulo Rodrigues Professional Services / MIS prodrigues@vianetworks.pt VIA NET.WORKS Portugal http://www.vianetworks.pt local touch global reach ------------------------- Sabe quem tem acesso à rede da sua empresa? http://security.vianetworks.pt/sig/
What is a "tree-like" queries? Or you mean a query returns you a hierarchy structure? (Thus tree like) On Tue, 15 Jan 2002 13:39:11 +0000 Paulo Rodrigues <prodrigues@vianetworks.pt> wrote: > Hello all, > > how do you usually handle hierarchical (tree-like) queries? I'm sure > someone else needed this already. > > Thanks in advance, > Paulo > > -- > Paulo Rodrigues > Professional Services / MIS > prodrigues@vianetworks.pt > > VIA NET.WORKS Portugal > http://www.vianetworks.pt > local touch global reach > ------------------------- > http://security.vianetworks.pt/sig/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Wei Weng Network Software Engineer KenCast Inc.
Hi Wei, An example would be a Bill of Materials; with Oracle I'd do select level, item_id, parent_id, quantity from boms start with parent_id='A' connect by prior parent_id=item_id; This is what I understand for a "tree-like" query. If that's the wrong name for it, it's my mistake, I'm sure. Thanks, Paulo Wei Weng wrote: > What is a "tree-like" queries? Or you mean a query returns you a hierarchy > structure? (Thus tree like) > > On Tue, 15 Jan 2002 13:39:11 +0000 > Paulo Rodrigues <prodrigues@vianetworks.pt> wrote: > > >>Hello all, >> >>how do you usually handle hierarchical (tree-like) queries? I'm sure >>someone else needed this already. >> >>Thanks in advance, >>Paulo >> >>-- >>Paulo Rodrigues >>Professional Services / MIS >>prodrigues@vianetworks.pt >> >>VIA NET.WORKS Portugal >>http://www.vianetworks.pt >>local touch global reach >>------------------------- >>http://security.vianetworks.pt/sig/ >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Don't 'kill -9' the postmaster >> >> > > -- Paulo Rodrigues Professional Services / MIS prodrigues@vianetworks.pt VIA NET.WORKS Portugal http://www.vianetworks.pt local touch global reach ------------------------- Sabe quem tem acesso à rede da sua empresa? http://security.vianetworks.pt/sig/
Paulo, > how do you usually handle hierarchical (tree-like) queries? I'm sure someone > else needed this already. Yup! Please see: http://techdocs.postgresql.org/ ... which has at least one article on trees. Also, Joe Celko's book, SQL for Smarties ( reviewed athttp://techdocs.postgresql.org/bookreviews.php ) has chapters on thetwo mainapproaches to tree structures in fairly exhaustive detail. Buy the book. Or,the condensed version of some ofthat information was posted to the list byJoe Celko last summer; you can hunt for it in the archives. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Paolo, I think this is an item on the TODO list under exotic features. (recursive unions). You could get around this using plpgsql, follow the link below for examples: http://www.brasileiro.net/postgres/cookbook/view-recipes.adp?section_id=2&fo rmat=long hih steve boyle ----- Original Message ----- From: "Paulo Rodrigues" <prodrigues@vianetworks.pt> To: "Wei Weng" <wweng@kencast.com> Cc: <pgsql-sql@postgresql.org> Sent: Tuesday, January 15, 2002 4:46 PM Subject: Re: [SQL] Hierarchical queries > Hi Wei, > > An example would be a Bill of Materials; with Oracle I'd do > > select level, item_id, parent_id, quantity from boms start with > parent_id='A' connect by prior parent_id=item_id; > > This is what I understand for a "tree-like" query. If that's the wrong > name for it, it's my mistake, I'm sure. > > Thanks, > Paulo > > Wei Weng wrote: > > > What is a "tree-like" queries? Or you mean a query returns you a hierarchy > > structure? (Thus tree like) > > > > On Tue, 15 Jan 2002 13:39:11 +0000 > > Paulo Rodrigues <prodrigues@vianetworks.pt> wrote: > > > > > >>Hello all, > >> > >>how do you usually handle hierarchical (tree-like) queries? I'm sure > >>someone else needed this already. > >> > >>Thanks in advance, > >>Paulo > >> > >>-- > >>Paulo Rodrigues > >>Professional Services / MIS > >>prodrigues@vianetworks.pt > >> > >>VIA NET.WORKS Portugal > >>http://www.vianetworks.pt > >>local touch global reach > >>------------------------- > >>http://security.vianetworks.pt/sig/ > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 4: Don't 'kill -9' the postmaster > >> > >> > > > > > > > -- > Paulo Rodrigues > Professional Services / MIS > prodrigues@vianetworks.pt > > VIA NET.WORKS Portugal > http://www.vianetworks.pt > local touch global reach > ------------------------- > Sabe quem tem acesso à rede da sua empresa? > http://security.vianetworks.pt/sig/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >