Re: Modified Preorder Tree Traversal - Advanced question

[Roland Bouman]

Got it!

select r.name
, d.name
, d.lft
, d.rgt
, d.zoom
from regions r
inner join regions d
on r.lft <= d.lft
and r.rgt >= d.rgt
where r.name = 'root'
and d.zoom <= 6
and not exists (
select null
from regions z
where r.lft < z.lft
and r.rgt > z.rgt
and z.lft < d.lft
and z.rgt > d.rgt
and z.zoom <= 6
and z.zoom >= d.zoom
)


Here, 'root' is the entry point, 6 is the zoom level of choice.

This is the output:

+------+-------------+------+------+------+
| name | name | lft | rgt | zoom |
+------+-------------+------+------+------+
| Root | UK | 2 | 9 | 5 |
| Root | Texas | 11 | 14 | 6 |
| Root | Connecticut | 15 | 18 | 3 |
+------+-------------+------+------+------+


The solution has two faces:

1) selecting all nodes from the entry point (root - r) down (descendants - d), already pruning for the target zoom factor
2) for each descendant, looking up to all ancestors (z - sorry no nice alias) up to (but not including) root, to exclude that there is an ancestor with a 'better' zoom factor.

Hope this is what you need.