My data is in table with 2 field, Id & ParentId. I store data with this structure(similar image in follow). How can I get all path from leaf to root that include Id = 6 ? (Result sample is in follow)
--Data structure is as follow :
-- 1
-- /
--2 <- 3 9
-- \ \ /
-- 4 <- 5 7 8
-- \ / / /
-- 6 - - -
-- / \
-- 10 <- 11
-- /
--12
--Data In Table Is :
--Id ParentId
--1 null
--2 1
--3 2
--4 2
--5 3
--5 4
--6 4
--6 5
--6 7
--6 8
--7 9
--8 null
--9 null
--10 6
--11 6
--11 10
--12 10
--Result for all trees that include "Id = 6":
--12 > 10 > 6 > 4 > 2 > 1
--12 > 10 > 6 > 5 > 4 > 2 > 1
--12 > 10 > 6 > 5 > 3 > 2 > 1
--12 > 10 > 6 > 7 > 9
--12 > 10 > 6 > 8
--11 > 10 > 6 > 4 > 2 > 1
--11 > 10 > 6 > 5 > 4 > 2 > 1
--11 > 10 > 6 > 5 > 3 > 2 > 1
--11 > 10 > 6 > 7 > 9
--11 > 10 > 6 > 8
--11 > 6 > 4 > 2 > 1
--11 > 6 > 5 > 4 > 2 > 1
--11 > 6 > 5 > 3 > 2 > 1
--11 > 6 > 7 > 9
--11 > 6 > 8
Your table says 4 has itself as a parent but not anything else, yet you have one row that states that 12 > 10 > 6 > 5 > 4 > 2 > 1 so I can't produce the same result with that setup.
my sqlfiddle for this is here: http://sqlfiddle.com/#!6/873b9/3
Assuming 4 has 2 as parent my code looks like this (ordering might be a little different but its SQL so it's ok):
WITH records as
(
SELECT
leaf.Id
,leaf.ParentId
,case when NOT EXISTS(SELECT * FROM recTest where ParentId = leaf.Id) then 1 else 0 end as isLeaf
FROM recTest as leaf
)
,hierarchy as
(
SELECT Id
,NULL as ParentId
,cast(Id as varchar(100)) as chain
,isLeaf
FROM records
where ParentId IS NULL
UNION ALL
SELECT r.Id
,r.ParentId
,cast(cast(r.Id as varchar(100)) + ' > ' + h.chain as varchar(100)) as chain
,r.isLeaf
FROM records as r
INNER JOIN hierarchy as h
ON r.ParentId = h.Id
)
SELECT
h.chain
FROM hierarchy as h
WHERE isLeaf = 1
AND h.chain like '%6%'
OPTION (MAXRECURSION 0)
For a table like this sample table, check this query:
with AllPossiblePath as(
SELECT distinct [descendant] leaf
,(
SELECT cast(f.dirname as nvarchar(64) )+'/'
FROM filesystem f JOIN tree_path t
ON t.ancestor = f.id
WHERE t.descendant=t1.descendant for xml path('')
) possiblePath
FROM [db1].[dbo].[tree_path] t1
where [descendant] not in(
SELECT TOP 1000 ancestor
FROM [db1].[dbo].[tree_path]
where ancestor!=[descendant])
)
select * from AllPossiblePath where possiblePath like '%Dir2%'
Hope this help!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With