New answers tagged

9

This recursive CTE (SQL Fiddle) should work with your sample: WITH cte(ParentID) AS( SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet' UNION ALL SELECT i.ParentID FROM cte c INNER JOIN @Instances i ON c.ParentID = i.InstanceID WHERE i.ParentID > 0 ) SELECT ParentID, count(*) FROM cte GROUP BY ParentID ORDER BY ParentID ; ...


0

I'm not sure I understand what you mean by "amount" and where table (?) PartInstances and columns id and count come from in your sample but I calculated what I guess from your sample data. ;with ins as ( select [InstanceID], [Part],[ParentID],0 lvl from instances where ParentID=0 union all select i.[InstanceID], i.[Part],i.[ParentID], lvl+1 from instances i ...


2

A recursive view is just syntactic sugar for a recursive CTE. create or replace recursive view foo (name, groupid, parentid) as select name, groupid,parentid from rl.groups where groupid=2 union SELECT groups.name, groups.groupid, groups.parentid FROM rl.groups AS groups, foo WHERE groups.groupid = foo.parentid ; is ...



Top 50 recent answers are included