Hi Svein

Thanks for your continue support. I tried your example but it fails on the line after UNION ALL at the place of MyTree.iID

SQL Message : -206
Column does not belong to referenced table

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -206
Column unknown
MYTREE.IID
At line 4, column 53

on the other hand if I change the same MyTree.iID to 62 then I got one of my requirement fulfilled that is to include the parent (ie 62) records also without duplication that I had noticed in my way of doing it.

As for infinite recursion, still no joy.

Thanks and regards.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> 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