--- In firebird-support@yahoogroups.com, Nols Smit <nols@...> wrote:
>
> Hi,
>
> 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

If you use FB >= 2.5, you'd best use a recursive CTE:

WITH RECURSIVE rt (
select id,description from locality where id=:ID_ROOT
union all
select id,description from locality where parent_id=rt.id
)

select * from rt

(from the top of my head, minor syntax errors possible - but you
should get the idea)

cheers,
Frank