>> Hi,
>>
>> I have a table that is self-referencing in a 1-M relationship. Think
>> of it as parent---children. Only a single parent för each child, which
>> implies this is a species of hermaphrodites. ;-)
>>
>> I need to select each record along with its ultimate
>> grandgrandgrand....parent's name.
>>
>> How would I do this in FB? Version 2.1 but I could upgrade to 2.5 if
>> it would help (it's in the pipe anyway).
>>
>
>OK, I solved it using a recursive CTE select. Something like this (pseudo - my real tables and columns are quite different):
>
>with recursive
> "Tree" as (
> select "Id",
> "Name" "OwnName",
> "Name" "AncestorName"
> from "Persons"
> where BI."ErsattAv" is null
> union all
> select P."Id",
> P."Name" "OwnName",
> T."AncestorName" "AncestorName"
> from "Persons" P
> inner join "Tree" T on T."Id" = P."Parent"
> )
>select "Id", "OwnName", "AncestorName"
>from "Tree"

Alternatively, you could traverse the tree the other way (in case you want the ancestor of one or a few specific persons and not the entire table):

with recursive "Tree" ("Id", "OwnName", "AncestorName", "Parent") as
(select "Id", "Name", "Name", "Parent"
from "Persons"
where "Name" = :Name
union all
select P."Id", C."OwnName", P."Name", P."Parent"
from "Persons" P
inner join "Tree" C on P."Id" = C."Parent"
)
select "Id", "OwnName", "AncestorName"
from "Tree"
where "Parent" is null

If you replace where "Parent" is null with where "AncestorName" = :AncestorName, the query would return a row if :Name was a descendant of :AncestorName, and nothing if it wasn't.

> For example:
> Name [parent]
> -----------------
> Peter [null]
> Steven [null]
> Bill [Peter]
> Eva [Peter]
> John [Bill]
> Susie [Bill]
> Megan [Susie]
> Keith [Eva]
> Richard [Steven]
>
> All of these are descendants to Peter or Steven. I would like to
> select
> this:
> Peter; Peter
> Steven; Steven
> Bill; Peter
> Eva; Peter
> John; Peter
> Susie; Peter
> Megan; Peter
> Keith; Peter
> Richard; Steven

HTH,
Set