0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/12838500/recursive-query-to-get-all-descendants-of-row

Q&A about the site for expert genealogists and people interested in genealogy or family history

DECLARE @menu table(id int, parent int, [text] varchar(50), URL varchar(50));

DECLARE @targetId INT = 2 ;--for example

INSERT INTO @menu VALUES
(2, NULL, 'Job Control', '~/Default.aspz'),
(3, 2, 'Job Dashboard', '~/JobDashboard.aspx'),
(6, NULL, 'CVR Control', '~/CVRDashboard.aspx'),
(8, 6, 'CVR', '~/cvr.aspx'),
(9, NULL, 'Admin', NULL),
(10, 9, 'HR', '~/Admin/HR.aspx'),
(11, 9, 'JNIC', '~/Admin/NIC.aspx');
WITH MenuCTE AS
(
  SELECT Id, parent, Text, Url
  FROM @menu
  WHERE Id = @targetId
  UNION ALL
  SELECT p.Id, p.parent, p.Text, p.Url
  FROM MenuCTE c
  INNER JOIN @menu p ON c.parent = p.id
)
SELECT *
FROM MenuCTE ;

fork query

edited dec 7 12 anonymous

created oct 11 12 Mahmoud Gamal