Which Firebird version are you using, Bhavbhuti? In Firebird 2.1, WITH RECURSIVE was introduced and you can now do things like

WITH RECURSIVE MyTree(PersonID) as
(SELECT p.ID FROM Person p WHERE p.ParentID is null
UNION
SELECT p.ID FROM Person p2 WHERE p2.ParentID = MyTree.PersonID)
SELECT p3.ID, p3.Name
FROM Person p3
JOIN MyTree m on p3.ID = m.PersonID

(at least this is how I think the syntax is, I rarely use it myself)

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Bhavbhuti Nathwani
Sent: 27. mars 2009 06:21
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Self-Joined Items Master and transactions stock ledger

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.