Re: [GENERAL] Implementing hierarchy

I have a similar problem. I can tell you how to get subcategories and
sub-sub categories with unions and self-joins, but it sounds like you've
already worked that out. I don't know how to get sub-categories down to
an arbitrary depth (I think this is the crux of your question), so I
have joins that go down several levels, as many as I need.


Mike Frisch wrote:
>
> I am trying to write code to access a product catalog (more as a learning
> exercise than anything else) and need to implement some sort of searchable
> hierarcy.  For example:
>
> Computer Hardware (toplevel)
>    Hard Drives
>       Internal
>          SCSI
>             Fast SCSI
>             Wide SCSI
>             SCA
>
> Assuming these 'categories' are all in the same table as follows:
>
> prkey   (primary key)
> descr   varchar
> parent  (for subcategories, toplevel parent is 0)
>
> Is it possible to formulate an SQL query to give me the hierarchy for SCA
> hard drives?  (with "Computer Hardware", "Hard Drives", "SCSI", "SCA" in
> the result set)  I've been experimenting with self-joins, but cannot see
> how to extend it for an arbitrary number of subcategories.  If I have the
> primary key for an item listed as being an "SCA hard drive", how do I get
> it's parents (subcategories and toplevel parent)?
>
> Pointers to documentation/books/web sites with this sort of information
> are greatly appreciated.
>
> Much thanks in advance.
>
> Mike.
>
> ======================================================================
>   Mike Frisch                         Email: mfrisch@saturn.tlug.org
>   Northstar Technologies        WWW: http://saturn.tlug.org/~mfrisch
>   Newmarket, Ontario, CANADA
> ======================================================================