October 7, 2009 at 12:38 pm
i am a newbie in sql server can someone help me with this
consider some inventory data:
AssemblyKey AssemblyName ParentKey
0 System NULL
1 System NULL
2 SubAssembly 0
3 SubSubAssembly 2
4 SubAssembly 1
5 SubAssembly 0
In this case, we have two systems (ParentKey is NULL) with the following descendents
System (key = 0)
> Sub assembly (key =2)
> Sub sub assembly (key=3)
> Sub assembly (key=5)
System (key=1)
> sub assembly (key=4)
How would you write a query to list the total number of descendents for each system, i.e., ?
AssemblyKey DescendentCount
0 3
1 1
i need the count for alld escendents of root parent
plz help !!!!!!
October 7, 2009 at 12:41 pm
I'm sure we'd love to help, but first, would you show us what you have done so far to solve your problem. It would probably help if you also provided the DDL for your tables, sample data in a readily consummable format, and what the expected results should be based on the sample data.
If you need help with this, please read and follow the instructions in the first article regarding asking for assistance in my signature block below.
October 7, 2009 at 12:48 pm
the stable stucture is
session_binding(assemblykey int,AssemblyName varchar, ParentKey int)
i am sorry to say but i am not confortable with sql so i have nothing worthwhile to show
i basically work with .net ,this is an emergency so i am asking
can you plz help me to count the descendents of the roots as i specified in the problem above
AssemblyKey AssemblyName ParentKey
0 System NULL
1 System NULL
2 SubAssembly 0
3 SubSubAssembly 2
4 SubAssembly 1
5 SubAssembly 0
here for assemby key 0 , 2 and 5 are the direct decendents .assemblykey = 3 is descendent of assembly key = 2 .
so in total there are 3 descendents of assemblykey = 0 .(2,3,5)
so i need these counts
hope can help
October 7, 2009 at 12:50 pm
Here's a sample of how to make this kind of thing work:
create table #T (
ID int identity primary key,
ParentID int null);
insert into #T (ParentID)
select null;
insert into #T (ParentID)
select null;
insert into #T (ParentID)
select 1;
insert into #T (ParentID)
select 2;
insert into #T (ParentID)
select 3;
;with Hierarchy (TopID, ID, ParentID) as
(select ID, ID, ParentID
from #T
where ParentID is null
union all
select TopID, T2.ID, T2.ParentID
from #T T2
inner join Hierarchy
on T2.ParentID = Hierarchy.ID)
select TopID, count(*) as Nodes
from Hierarchy
group by TopID
order by TopID;
You should be able to modify the final query to work on your table pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply