Den 2013-08-16 12:31 skrev Kjell Rilbe såhär:

>
> 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"

>
> 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
>
> Regards,
> Kjell
>
> --
> --------------------------------------
> Kjell Rilbe
> DataDIA AB
> E-post: kjell@... <mailto:kjell%40datadia.se>
> Telefon: 08-761 06 55
> Mobil: 0733-44 24 64
>
>

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64