--- In firebird-support@yahoogroups.com, "diwic2" <diwic2@...> wrote:

>
> I have a self-referencing table, something like:
>
> id integer
> name varchar(98)
> parentid integer (references another row in the same table)
>
> say I have these rows:
> ID ParentID Name
> 1 <null> World
> 2 1 Europe
> 3 2 Sweden
> 4 2 Germany
> 5 3 Stockholm
> 6 3 Gothenburg
> 7 4 Berlin
>
>
> Now is there a smart SQL query that for one or more id's, will return
> the actual rows, including their ancestors? That is, if I do a select
> "where ID=5", I would like rows 5,3,2 and 1 returned ("Stockholm",
> "Sweden","Europe","World").
>

I would split this into two tables, one to define the records and one
to define the relationships. It makes manipulating the data much easier.

SomeTable
(
id integer
name varchar(98)
)

SomeTableParent
(
id integer (references sometable(id), unique contraint)
parentid integer (references sometable(id)
)

The next step would be to define a recursive stored procedure. I have
not tested this, but I imagine it would look something similar to below.

create procedure blah
(
someid integer
)
returns
(
id integer,
name varchar(98),
parentid integer
)
as
begin
for
select s.id, s.name, p.parentid
from sometable s
left join sometableparent p on (s.id = p.id)
where s.id = :someid
into :id, :name, :parentid;
do
begin
suspend;

if (:parentid is not null) then
begin
for
select id, name, parentid
from blah (:parentid)
into :id, :name, :parentid;
do
begin
suspend;
end
end
end
end
^