Thread: Hierarchical queries

Hierarchical queries

From
Paulo Rodrigues
Date:
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/



Re: Hierarchical queries

From
Wei Weng
Date:
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.



Re: Hierarchical queries

From
Paulo Rodrigues
Date:
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/



Re: Hierarchical queries

From
"Josh Berkus"
Date:
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
 


Re: Hierarchical queries

From
"Steve Boyle \(Roselink\)"
Date:
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
>