Try

WITH RECURSIVE MyTree(iID) AS
(SELECT p.iID FROM mItems p WHERE p.iID = 62
UNION ALL
SELECT p2.iID FROM mItems p2 WHERE p2.iPID = MyTree.iID)

SELECT p3.*
FROM sGoodsRecvNoteItem p3
JOIN MyTree m ON p3.iItemID = m.iID
ORDER BY iPID, iSrNo

I'm not 100% certain this is the right syntax, but I'm certain that it is what you have after UNION ALL that has to specify the recursion in a similar way to what is above. Having a constant in that part makes it only go one level deep, as you observed.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Bhavbhuti Nathwani
Sent: 27. mars 2009 11:32
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Self-Joined Items Master and transactions stock ledger

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