Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how can select all child account for   parent root      sql query

Posted on 2010-09-22
6
Medium Priority
?
499 Views
Last Modified: 2012-05-10
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
Comment
Question by:yexperts
  • 3
  • 2
6 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33740984
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
 
LVL 7

Expert Comment

by:rashmi_vaghela
ID: 33741056
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
 

Author Comment

by:yexperts
ID: 33741310
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 58

Accepted Solution

by:
cyberkiwi earned 750 total points
ID: 33743028
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
 

Author Closing Comment

by:yexperts
ID: 33765511
thanks
0
 

Author Comment

by:yexperts
ID: 36015549
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question