How to get all children of a parent and then their children using recursion in query
recursive-query, sql-server-2008, tree
Solution
DECLARE @Id int = your_UnitId
;WITH cte AS
(
SELECT a.Id, a.parentId, a.name
FROM customer a
WHERE Id = @Id
UNION ALL
SELECT a.Id, a.parentid, a.Name
FROM customer a JOIN cte c ON a.parentId = c.id
)
SELECT parentId, Id, name
FROM cte
Demo on SQLFiddle
Problem
I have structure like this: ``` <Unit> <SubUnit1> <SubSubUnit1/> <SubSubUnit2/> ... <SubSubUnitN/> </SubUnit1/> <SubUnit2> <SubSubUnit1/> <SubSubUnit2/> ... <SubSubUnitN/> </SubUnit2/> ... <SubUnitN> <SubSubUnit1/> <SubSubUnit2/> ... <SubSubUnitN/> </SubUnitN/> </Unit> ``` This structure has 3 levels: main Unit, SubUnits and SubSubUnits. I want to select all children by UnitId. If I search by Unit, I have to get all tree. If I search by SubUnit1, I have to get SubUnit1 and all children of SubUnit1. If I search SubSubUnit2, I have to get itself. Here is my try: ``` with a(id, parentid, name) as ( select id, parentId, name from customer a where parentId is null union all select a.id, a.parentid, a.Name from customer inner join a on customer.parentId = customer.id ) select parentid, id, name from customer pod where pod.parentid in ( select id from customer grbs where grbs.parentid in ( select id from customer t where t.parentid = @UnitId )) union select parentid, id, name from customer grbs where grbs.parentid in ( select id from customer t where t.parentid = @UnitId ) union select parentid, id, name from customer c where c.Id = @UnitId order by parentid, id ``` I use 3 union-words, it is not well but it works. Case structure will have N levels, how I have to get correct result?