Self Join Table (Recursive Hierarchy)
-
i Have Self join Table in Sql server Database(accounting database) this is table columns(Accounts Table) AccountID ParentAccountID AccountNo AccountName and i have another table (Accounts Balance Table) AccountBalanceID AccountID FirstBalanceCredit FirstBalanceDebit TotalCredit TotalDebit in accountbalance i add balance for accounts in last level which is not parent for any other accounts so my question is how to get sum of debit or credit in any level for example if i have accountx which is parent so it doesn't have any rows in accountbalance because it's not last level i need to get total debit for that account which is the total accounts for all accounts under him which may not be the last level thanks for any help
md_refay
-
i Have Self join Table in Sql server Database(accounting database) this is table columns(Accounts Table) AccountID ParentAccountID AccountNo AccountName and i have another table (Accounts Balance Table) AccountBalanceID AccountID FirstBalanceCredit FirstBalanceDebit TotalCredit TotalDebit in accountbalance i add balance for accounts in last level which is not parent for any other accounts so my question is how to get sum of debit or credit in any level for example if i have accountx which is parent so it doesn't have any rows in accountbalance because it's not last level i need to get total debit for that account which is the total accounts for all accounts under him which may not be the last level thanks for any help
md_refay
with Account(AccountID)
As
(
select AccountID from Accounts A where AccountID = #AcountNumber#
union all
select A.AccountID from Accounts A, Account B
where A.ParentAccountID = B.AccountID
)SELECT sum(totaldebit) TotalDebit FROM Account, AccountsBalance where
Account.AccountID = AccountsBalance.AccountIDreplace #AcountNumber# with your account number (probably use variable/parameter). Hope this will help you :cool: