Non-Homogenous Hierachy

I have a request to make a hierarchy where by each level in the tree is not
from the same table.
Originally it was ok the first 3 levels where of the same table so I simply
added a ParentID field and make a self referencing foreign key to the table.
However, after the 3rd level the items come from 3 other tables.
Categories Table
Level1
Level2
Level3
Other Table1
Level4
Other Table2
Level5
The linkage is ok but I need to generate/populate a treeview object from
this. Forming the tree is a bit awkward as when a node is selected I need
to set in the database that the child nodes are selected. Normally I could
use a CTE expression to traverse the tree but depending on what level you
select you need to traverse different tables which makes the statement
difficult.
I'd love to find a way to represent the hierarchy in a single table so I can
do parent/child ID lookups in the same table.
Any thoughts/suggestions really appreciated.
TIA
MattCOn Jun 14, 12:02 pm, "MattC" <m...@.m.com> wrote:
> I have a request to make a hierarchy where by each level in the tree is no
t
> from the same table.
> Originally it was ok the first 3 levels where of the same table so I simpl
y
> added a ParentID field and make a self referencing foreign key to the tabl
e.
> However, after the 3rd level the items come from 3 other tables.
> Categories Table
> Level1
> Level2
> Level3
> Other Table1
> Level4
> Other Table2
> Level5
> The linkage is ok but I need to generate/populate a treeview object from
> this. Forming the tree is a bit awkward as when a node is selected I need
> to set in the database that the child nodes are selected. Normally I coul
d
> use a CTE expression to traverse the tree but depending on what level you
> select you need to traverse different tables which makes the statement
> difficult.
> I'd love to find a way to represent the hierarchy in a single table so I c
an
> do parent/child ID lookups in the same table.
> Any thoughts/suggestions really appreciated.
> TIA
> MattC
Hi
Could you not create a view that Unions the three tables? There is no
information on the table structures (DDL), example data or what you
are returning to the treeview!! (see [url]http://www.aspfaq.com/etiquette.asp?id=5006[/
url])
A different approach may be to use XML and the ability in SQL 2005 to
have nested statements.
John