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

yexpertsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
cyberkiwiConnect With a Mentor Commented:
Q1:

-- as result rows
;with tmp as (
      select Fld_ACCOUNT_ID, Fld_PARENT_ID as Par
      from tbl_account
      where Fld_PARENT_ID = 2 ---- this is the parameter
      union all
      select t.Fld_ACCOUNT_ID, t.Fld_PARENT_ID as Par
      from tmp
      inner join tbl_account t on t.Fld_PARENT_ID=tmp.Fld_ACCOUNT_ID
)
select Fld_ACCOUNT_ID from tmp
except
select Par from tmp

-- as a single string
;with tmp as (
      select Fld_ACCOUNT_ID Acc, Fld_PARENT_ID as Par
      from tbl_account
      where Fld_PARENT_ID = 2 ---- this is the parameter
      union all
      select t.Fld_ACCOUNT_ID, t.Fld_PARENT_ID
      from tmp
      inner join tbl_account t on t.Fld_PARENT_ID=tmp.Acc
)
select stuff(
      (select ','+cast(Acc as varchar(10))
      from (
            select Acc from tmp
            except
            select Par from tmp) X
      for xml path('')), 1, 1, '') as LeavesOf2
0
 
cyberkiwiCommented:
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
0
 
rashmi_vaghelaCommented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
yexpertsAuthor Commented:
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


0
 
yexpertsAuthor Commented:
thanks
0
 
yexpertsAuthor Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.