Thanks Svein for your response. Yes, I am using 2.1 and I took your example and tried to do a recursive as follows:

WITH RECURSIVE MyTree(iID) AS
(SELECT p.iID FROM mItems p WHERE p.iPID IS NULL
UNION ALL
SELECT p2.iID FROM mItems p2 WHERE p2.iPID = 62)
SELECT p3.*
FROM sGoodsRecvNoteItem p3
JOIN MyTree m ON p3.iItemID = m.iID
ORDER BY iPID, iSrNo

Not wanting to be a gripe for such a great facility added to FireBird but maybe I missed something. Because sadly I get only the next level of children. Thus returned a set of iItemID = 99 and 100 which are children of 62. Whereas it did not pull the records for iItemID = 258 which is a child item of 99.

So does this mean I have to code the UNION ALL for as many level as I want, or did I miss something. If so what will that part be coded like what?

Also can I get an inclusive WITH RECURSIVE? In the above example 62 itself has transactions. Doing an OR p3.iItemID = 62 under the JOIN returned duplication of records belonging to 62.

Please advise.

Thanks a lot and regards.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> Which Firebird version are you using, Bhavbhuti? In Firebird 2.1, WITH RECURSIVE was introduced and you can now do things like