@Svien: Like you said typing in an email client can have its problem I have updated your query where required and pasted it here below and yes, it does bring out the data I was expecting. Thanks a lot. Tell me how do you do this :)

@Thierry: Thanks for your response.


WITH RECURSIVE MyCTE(iid, MyLevel, n0, n1, n2, n3) as
(SELECT lvl0.iid, cast(0 as Integer), lvl0.iid, cast(null as Integer),
cast(null as Integer), cast(null as Integer)
FROM mItems lvl0
WHERE lvl0.ipid = 0
UNION ALL
SELECT lvlc.iid, lvlp.MyLevel + 1, lvlp.iid,
case
when lvlp.MyLevel = 0 then lvlc.iid
else lvlp.n1
end,
case
when lvlp.MyLevel = 1 then lvlc.iid
else lvlp.n2
end,
case
when lvlp.MyLevel = 2 then lvlc.iid
else lvlp.n3
end
FROM MyCTE lvlp
JOIN mItems lvlc ON lvlp.iid = lvlc.ipid
WHERE lvlp.MyLevel < 3)

SELECT cte.n0, cte.n1, cte.n2, cte.n3, lvl.cDesc, cte.iid
FROM MyCTE cte
JOIN mItems lvl on cte.iid = lvl.iid

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> If Thierry is right (and that what you perceive is loss, is actually those parents that have child records), then you either have to write a stored procedure or use CTEs (admittedly, I've never used CTEs like this before):