[DISCUSSION] Recursive/Hierarchical Select

[Bart Pietercil]

Hi Ruslan,

continuing discussion from here;


On 04 Feb 2009, at 12:56, Ruslan Zasukhin wrote:

> On 2/4/09 1:12 PM, "Bart Pietercil" <bart.pietercil at cognosis.be>  
> wrote:
>
> Hi Bart,
>
>> First questions for me is how do you specify what a descendant IS ?
>> Shouldn't you specify a link name ?
>
> Well, good point.
>
> On API level if you have check this Mantis reports
>>  http://www.valentina-db.com/bt/view.php?id=3958.
>>  http://www.valentina-db.com/bt/view.php?id=3989.
>>  http://www.valentina-db.com/bt/view.php?id=3960
>
> It is part of Vlink method ...
>
> For SQL we really must mention name of link somehow.
> Damn, it is always good make discussion :-)
>
>> Since it is entirely possible that a table is instead of a tree a
>> matrix (more than one tree in the same table) but a self-join link is
>> clear (identifies a specific tree in the table)
>
>> So since it is possible to have multiple self-join links in the  
>> table,
>> how will the proposed syntax handle the link to use ?
>
> Okay, what about next improvement
>
> WHERE (recid IN DESCENDANTS linkName OF (recID = 5) ON LEVEL 3)

I think with a few variations on this theme we have most covered:

1) for selection of records on a specified level (

select * from T1 where (recid IN DESCENDANTS linkname OF (recid = 5)  
ON LEVEL 3)

select * from T1 where (recid IN ASCENDANTS linkname OF (recid = 5) ON  
LEVEL 3)



so this accounts for the case where you want only the results for the  
specified level upwards or downwards

2) for selection of records from starting recid till specified level


select * from T1 where (recid IN DESCENDANTS linkname OF (recid = 5)  
DOWN TO LEVEL 3 INCLUSIVE/EXCLUSIVE STARTLEVEL)

select * from T1 where (recid IN ASCENDANTS linkname OF (recid = 5) UP  
TO LEVEL 3 INCLUSIVE/EXCLUSIVE STARTLEVEL)

This selects all recid from starting recid all branches up to or down  
to the specified level and you can specify if you want the  
startinglevel inclusive or not.
Startinglevel : it seems to me that there will be case where you want  
to exclude the starting level (since you already know this level  
(otherwise you could not give a startinglevel recid))


I really hope we can get this soon. Our application uses recursif  
calls all the time.

tia

Bart Pietercil


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.macserve.net/pipermail/valentina/attachments/20090305/93d43e6a/attachment.html