For SQL Server 2005 and up it's a piece of cake.

See recursive CTE.

In VFP you would need to self-join (LEFT JOIN) with maximum possible level of recursion.

See SQL server sample here

http://forum.lessthandot.com/viewtopic.php?f=17&t=5146



>Sorry if this appears as a double post
>
>Hi All
>
>I have an Items master table that is self joinable representing the hierarchy of the items. Each item PK will have their own transactions in various tables. I have a stock ledger report that brings in transactions based on the given PKs, till here everything is fine.
>
>Now the users needs to select an item in the hierarchy that has children, grand-children, great-grand-children and maybe deeper levels and now to provide a stock ledger that consolidates the transactions of all these levels under a given Item PK.
>
>Is this possible using some magical SQL statements like
>

SELECT fields ;
>   FROM TransactTable TT ;
>      SOME JOINS MAYBE ;
>   WHERE TT.iItemID IN (SELECT MagicStatement WHERE iID = ?High-Level-PK)

>
>or do I have to resort to programming that drills downs the hierarchy and brings in transactions piece-meal into a cursor for consolidation purposes.
>
>Please advise.