• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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

0
yexperts
Asked:
yexperts
  • 3
  • 2
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
cyberkiwiCommented:
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now