Hi Svein

You are a super-hero :) You guided me all the while without any direct FireBird reference!!

The first 2 suggestions did not work but the new query you gave me worked without a hitch and returns the main PK records (62) it's children records (99, 100) and the grandchild (258) records.

Yes, I had referred to the Release Notes once you pointed me in the right direction with your first reply but I could not get me head around it.

I have tried deeper hierarchy and this is the query I was hoping to have, thanks a million.

Thanks again and regards.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> and see if that helps. If that fails, try
>
> WITH RECURSIVE MyTree(iID) AS
> (SELECT p.iID FROM mItems p WHERE p.iID = 62
> UNION ALL
> SELECT p2.iID FROM mItems p2
> JOIN MyTree M on p2.iPID = M.iID)
> SELECT p3.*
> FROM sGoodsRecvNoteItem p3
> JOIN MyTree m ON p3.iItemID = m.iID
> ORDER BY iPID, iSrNo