There are three new kind of queries specially designed for recursive (hierarchical) tables.
Such kind of tables are widely used. It could be human relationships, threaded forums and anything which might be described as tree-like data.
There are few common SQL ways to operate with such structures but all of them are too far from perfect.
Valentina introduces special kind of query which looks much more clear, natural and readable.
Syntax:
vext_recursive_table -- v4.1
: {ANCESTORS | BROTHERS | DESCENDANTS} OF vext_root_objects
[{TO|ON} LEVEL UINT] USING link_name [WITH {ROOT | ROOTS}]
vext_root_objects
: UINT
| variable_name
| ( search_condition )
Examples:
ANCESTORS OF (RecID IN(4,5)) ON LEVEL 1 USING l1 BROTHERS OF 4 ON LEVEL 2 USING l1 SELECT * FROM (ANCESTORS OF (RecID IN(4,5)) ON LEVEL 1 USING l1) WHERE f1 = 'Branch2'
See also:
* WIKI: Valentina SQL Reference: Hierarchical (Recursive) Queries for description of exact syntax of these commands and examples.
* WIKI: Article Hierarchical (Recursive) Queries in Valentina SQL