Hi Nols,

> I'm using a simple tree-structure with the following DDL:
>
> CREATE TABLE LOCALITY (
> ID INTEGER NOT NULL,
> PARENT_ID INTEGER,
> DESCRIPTION VARCHAR(50) NOT NULL
> );
>
> Typical data in this table will be:
>
>
> ID Parent_ID Description
> 1 null South Africa
> 2 1 Limpopo
> 3 1 Gauteng
> 4 1 Eastern Cape
> 5 1 Western Cape
> 6 1 Northern Cape
> 7 1 Mpumalanga
> 9 1 KwaZulu-Natal
> 10 1 Free State
> 11 3 Pretoria
> 12 5 Bellville
>
>
> The column ID is the primary key.
>
> Those with a Parent_ID = 1 are provinces and those referring to provinces are cities, etc.
>
> How do I iterate from a given primary key down through all the branches. I typically match a treeview in the program to the tree-structure and I want to show all the relevant records of a node and all its sub-nodes.

By using a recursive selectable stored procedure (all Firebird versions)
or a recursive CTE - common table expression - in Firebird 2.1 or up.


--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!