Link to home
Start Free TrialLog in
Avatar of yexperts
yexperts

asked on

how can select all child account for parent root sql query

hi
i have table named is tbl_account
 this field:
Fld_ACCOUNT_ID
Fld_PARENT_ID
Fld_ACCOUNT_NAME
Fld_AMOUNT

with this data
Fld_ACCOUNT_ID***Fld_PARENT_ID***Fld_ACCOUNT_NAME***Fld_AMOUNT
1                         *** 0                     *** a                              ***null
2                         *** 1                     *** b                              ***null
3                         *** 1                     *** c                               ***23
4                         *** 2                     *** d                              ***null
5                         *** 2                     *** e                              ***70
6                         *** 4                     *** f                               ***null
7                         *** 4                     *** j                               ***10
8                         *** 6                     *** h                              ***50

 I want a query which displays final child account when i get it PARENT ID
ex: if   (2)
     display:5,7,8  (final child for PARENT_ID=2)

also
 
I want a query which displays the amount for all PARENT and child account
ex:
Fld_ACCOUNT_ID***Fld_PARENT_ID***Fld_ACCOUNT_NAME***Fld_AMOUNT
1                         *** 0                      *** a              ***153   sum(all child in the root)
2                         *** 1                      *** b              ***130   sum(all child in the root)
3                         *** 1                       *** c              ***23
4             *** 2           *** d              ***60    sum(all child in the root)
5             *** 2           *** e              ***70    
6             *** 4           *** f              ***50    sum(all child in the root)
7             *** 4           *** j              ***10
8             *** 6           *** h              ***50

Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Replace #tbl below with the name of your real table

;with tmp as (
      select Fld_ACCOUNT_ID, Fld_ACCOUNT_ID as Par, Fld_AMOUNT
      from #tbl
      union all
      select tmp.Fld_ACCOUNT_ID, t.Fld_ACCOUNT_ID as Par, t.Fld_AMOUNT
      from tmp
      inner join #tbl t on t.Fld_PARENT_ID=tmp.Par
)
select t.Fld_Account_ID, t.Fld_PARENT_ID, t.Fld_ACCOUNT_NAME, sum(tmp.Fld_AMOUNT)
from tmp
inner join #tbl t on t.Fld_Account_ID = tmp.Fld_Account_ID
group by t.Fld_Account_ID, t.Fld_PARENT_ID, t.Fld_ACCOUNT_NAME
try this
WITH Emp_CTE AS (
SELECT Fld_ACCOUNT_ID,Fld_PARENT_ID,0 AS Generation,Fld_AMOUNT
FROM TempFld
WHERE Fld_ACCOUNT_ID = 2
UNION ALL
SELECT t.Fld_ACCOUNT_ID,t.Fld_PARENT_ID , Generation+1,Sum(t.Fld_AMOUNT)
FROM TempFld t
INNER JOIN Emp_CTE ecte ON ecte.Fld_ACCOUNT_ID= t.Fld_PARENT_ID
)
SELECT *
FROM Emp_CTE Where  Fld_ACCOUNT_ID Not In (Select  Fld_PARENT_ID from Emp_CTE)
GO
Avatar of yexperts
yexperts

ASKER

hi

thanks cyberkiwi
your answer  is right for Question #2


thanks rashmi_vaghela
but Unfortunately, your answer  for Question #1  is not true
error details

(Msg 8120, Level 16, State 1, Line 2
Column 'Tbl_Account.Fld_ACCOUNT_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 467, Level 16, State 1, Line 2
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'Emp_CTE'.
)


  still I try to solve Question #1


ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks
how can add this code  

select Fld_ACCOUNT_ID,Fld_ACCOUNT_NAME ,Fld_Level,Fld_PARENT_LEVEL,
isnull((SELECT sum(made)from CL_main where cl=Fld_ACCOUNT_ID  and dt <@date1 ),0)  bfrm,
isnull((SELECT sum(daen)from CL_main where cl=Fld_ACCOUNT_ID and dt <@date1 ),0)  bfrd,
isnull((SELECT sum(made)from CL_main where cl=Fld_ACCOUNT_ID  and dt between @date1 and @date2),0)  nowm,
isnull((SELECT sum(daen)from CL_main where cl=Fld_ACCOUNT_ID and dt between @date1 and @date2),0)  nowd,
(SELECT 0 )  netm,
(SELECT 0 )  netd
 from tbl_account

Open in new window


with this


with tmp as (
      select Fld_ACCOUNT_ID as Fld_ACCOUNT_ID , Fld_ACCOUNT_ID as Par, Fld_AMOUNT
--,
--(select Fld_ACCOUNT_ID from Tbl_Account where Fld_ACCOUNT_ID= )
      from Tbl_Account
      union all
      select tmp.Fld_ACCOUNT_ID, t.Fld_ACCOUNT_ID as Par, t.Fld_AMOUNT
      from tmp
      inner join Tbl_Account t on t.Fld_PARENT_LEVEL=tmp.Par
)
select t.Fld_Account_ID, t.Fld_PARENT_LEVEL, t.Fld_ACCOUNT_NAME, sum(tmp.Fld_AMOUNT)
from tmp
inner join Tbl_Account t on t.Fld_Account_ID = tmp.Fld_Account_ID
group by t.Fld_Account_ID, t.Fld_PARENT_LEVEL, t.Fld_ACCOUNT_NAME

Open in new window