can someone solve this plz - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

can someone solve this plz

Author

Topic

rakesh252378
Starting Member

1 Post

Posted - 2009-10-07 : 14:40:29

please help me out with this problem

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 alldescendent of root parent
plz help !!!!!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-07 : 15:04:22

Here is one way:

DECLARE @Inventory TABLE (AssemblyKey INT, AssemblyName VARCHAR(50), ParentKey INT)

INSERT @Inventory
SELECT 0, 'System', NULL 
UNION ALL SELECT 1, 'System', NULL 
UNION ALL SELECT 2, 'SubAssembly', 0 
UNION ALL SELECT 3, 'SubSubAssembly', 2 
UNION ALL SELECT 4, 'SubAssembly', 1 
UNION ALL SELECT 5, 'SubAssembly', 0 
;

WITH Inv
AS
(
	SELECT *, AssemblyKey AS TopParentKey, 0 AS AssemblyLevel
	FROM @Inventory
	WHERE ParentKey IS NULL
	
	UNION ALL
	
	SELECT I.*, Inv.TopParentKey, Inv.AssemblyLevel + 1
	FROM @Inventory AS I
	INNER JOIN Inv
	ON I.ParentKey = Inv.AssemblyKey
)


SELECT 
	A.AssemblyKey, COUNT(*)
FROM 
	@Inventory AS A
INNER JOIN
	Inv AS B
	ON A.AssemblyKey = B.TopParentKeY
	AND B.ParentKey IS NOT NULL
GROUP BY 
	A.AssemblyKey

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-07 : 15:17:01

I had already had this coded when I I saw Lamprey's solution. It is similar but not exactly the same so I'll post it anyway

This can be done with a recursive CTE (common table expression)


declare @t table (AssemblyKey int, AssemblyName varchar(20), ParentKey int)
insert @t
select 0, 'System', NULL
union all select 1, 'System', NULL
union all select 2, 'SubAssembly', 0
union all select 3, 'SubSubAssembly', 2
union all select 4, 'SubAssembly', 1
union all select 5, 'SubAssembly', 0

;with cte (sysKey, AssemblyKey, descendant)
as (
       select AssemblyKey
              ,AssemblyKey
              ,0
       from @t
       where  ParentKey is null

       union all

       select c.sysKey
              ,t.AssemblyKey
              ,1
       from   cte c
       join   @t t
              on t.ParentKey = c.AssemblyKey
       )
select sysKey
       ,sum(descendant) descendantCount
from   cte
group by sysKey

OUTPUT
sysKey      descendantCount
----------- ---------------
0           3
1           1

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-07 : 15:26:44

TG's solution is more effecient than mine, nice! :)

TG also gave me an idea which should speed mine up:

SELECT TopParentKey, COUNT(*)
FROM Inv
WHERE AssemblyLevel > 0
GROUP BY TopParentKey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources