Jim Crafton schrieb:
In my table(s) I need to model a class hierarchy, as well as a class/var/function belonging to a namespace, and/or a function/var belonging to a class. In other words a parent/child relationship. If I simply add a new column to my primary symbols table, call it "Parent", how would I query for a given row plus any children (and any of their children, and so on)? If this is too general an SQL question and it's not appropriate to the list, my apologies.
This is a classical problem, when working with trees and hierarchies. It was discussed here last week (?) when a person asked here about a special database layout.
In general it can not be done in ONE SQL statement unless the database gives you the power of some kind of recursive SQL queries. What other databases offers you can be read in either Joe Celko - SQL for Smarties (2000 or 2005) or Joe Celko - Trees and Hierarchies in SQL for Smarties (2005) In those books he mainly uses stored procedures to get the work done. Databases like DB2, MaxDB or Oracle offer you these recursive SQL statements. The main winning is, that you execute ONE query and retrieves all rows belonging to a sub tree, which is MUCH faster, that to execute multiple queries ... In a special case, where your items only belong to one main sub tree (and the sub trees are not interesting for you), you could also store the reference to this main sub tree (or the root of that tree) in each row. Summary: If you have a fixed/known levels of hierarchies this can be done in one query. Marten
