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