I wrote this question back on Tuesday 9/14/04, but apparently it did not get
posted, so I am trying again.



I have an application that represents objects in a tree like structure such
as:



+ Level 0

+--Level 1

+----Level 2

+------Level 3

..

+-------- Level n



Each object may be of a specific type represented by an integer I



Physically the database where this data is stored has two relevant tables,
item and relation.



Item.Id is the unique key for any object in item and each item has an
item_type which denotes the kind of object it is.



Relation has a source_id and ad source_type, and a target_id and a
target_type which represent objects from the item table where the source_id
is the parent and the target_id is the child.

Something like this:



+--Item--+ +--Relation--------+

+ Id1 +=======>+ Source_Id = Id1 +

+ Id2 +<=======+ Target_Id = Id2 + same record as above.

+ Id2 +=======>+ Source_Id = Id2 +

+ Id3 +<=======+ Target_Id = Id3 + same record as above.

+ . +

+ Id n-1 +=======>+ Source_Id = Id n-1 +

+ Id n +<=======+ Target_Id = Id n + same record as above.



I have been trying to create a view that will return all the descendants of
any given item, for example:



select descendant_id from ancestor_descendant_view where ancestor_id = 1



The only way I could figure to do this was from a series of unions that
would return the items offset by specified levels. This is not, in my
judgment, a very efficient or flexible query, but then

I could not figure out another way to achieve my desired results.



So my question to the group is does anybody have a better idea about how to
achieve the same results?



I want it to be a view because I intend to use this with Crystal Reports in
sub-reports, etc. and there seem to be limitations to tables produced by
stored procedures for the purposes of reporting.



Any help is greatly appreciated. - Henry Stock, MCSE



> Here is the view:

>

> /************************************************** ***********

> View: Ancestor_descendant

>

> The purpose of this view is to provide a mechanism for user reports to

> show all descendant of a given type that may be related by virtue of

> relationship tree.

>

> The view functions by allowing for unions of up to ten levels of

> descendants that may be related to a given anscestor.

>

> Usage: Select ancestor_id, descendant_id from ancestor_descendant

> where ancestor_type = 1 and descendant_type = 7

>

> Shows all Accounts and Related Issues

>

>

> ************************************************** ************/

>

> create view ancestor_descentant_view

> as

>

> -- Case: Direct_Relationship

> select a.target_id as ancestor_id,

> a.target_type as ancestor_type,

> tgt.target_id as descendant_id,

> tgt.target_type as descendant_type,

> tgt.org_id as org_id,

> 0 as descendant_level,

> tgt.order_index as descendant_order,

> tgt.source_id as descendant_parent_id, tgt.source_type as

> descendant_parent_type

>

> from dbo.relation as a

> left outer join dbo.relation as tgt

> on (a.target_id = tgt.source_id )

>

>

> union

>

>

> -- Case: One off relationship

> select a.target_id as ancestor_id,

> a.target_type as ancestor_type,

> tgt.target_id as descendant_id,

> tgt.target_type as descendant_type,

> tgt.org_id as org_id,

> 1 as descendant_level,

> tgt.order_index as descendant_order,

> tgt.source_id as descendant_parent_id, tgt.source_type as

> descendant_parent_type

>

> from dbo.relation as a

> left outer join relation as off_1

> on a.target_id = off_1.source_id

> left outer join dbo.relation as tgt

> on (off_1.target_id = tgt.source_id )

>

> union

>

> -- Case: Two off relationship

> select a.target_id as ancestor_id,

> a.target_type as ancestor_type,

> tgt.target_id as descendant_id,

> tgt.target_type as descendant_type,

> tgt.org_id as org_id,

> 2 as descendant_level,

> tgt.order_index as descendant_order,

> tgt.source_id as descendant_parent_id, tgt.source_type as

> descendant_parent_type

>

> from dbo.relation as a

> left outer join relation as off_1

> on a.target_id = off_1.source_id

> left outer join relation as off_2

> on off_1.target_id = off_2.source_id

> left outer join dbo.relation as tgt

> on (off_2.target_id = tgt.source_id )

>