Hi All

I have an Items master table that is self joined 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 = ?Highest-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.