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
> ======================================================================
