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_PAREN T_ID***Fld _ACCOUNT_N AME***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_PAREN T_ID***Fld _ACCOUNT_N AME***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
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_PAREN
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_PAREN
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
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_PAR ENT_ID , Generation+1,Sum(t.Fld_AMO UNT)
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
WITH Emp_CTE AS (
SELECT Fld_ACCOUNT_ID,Fld_PARENT_
FROM TempFld
WHERE Fld_ACCOUNT_ID = 2
UNION ALL
SELECT t.Fld_ACCOUNT_ID,t.Fld_PAR
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
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_I D' 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
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_I
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
ASKER
how can add this code
with this
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
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
;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