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.
If it's not broken, fix it until it is.
My Blog