Solved

how can select all child account for   parent root      sql query

Posted on 2010-09-22
6
496 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 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

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

636 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