July 25, 2016 at 12:56 pm
Hi,
I need help with my CTE; All I need to find is the match between groupID and memberID and set nodelevel; once the result is already there, I want my CTE to stop. However, it is right now it is going in infinite loop because of 9,8.
I want my result to look like this:
groupIDobjectType memberIDNodelevel
8 group 1 0
1 group 9 1
1 user 7 1
9 group 8 2
---CODE---
Currently, results are going in infinite loop.
I have Hierarchy table:
drop table #Hierarchy
CREATE TABLE #Hierarchy( groupID int , memberID int, objectType varchar(max))
insert into #Hierarchy ( groupID , memberID , objectType ) values (8, 1,'group')
insert into #Hierarchy ( groupID , memberID , objectType ) values (1, 9,'group')
insert into #Hierarchy ( groupID , memberID , objectType ) values (1, 7,'user')
insert into #Hierarchy ( groupID , memberID , objectType ) values (9, 8,'group')
WITH CTE( groupID, objectType, memberID, [Nodelevel]) as
(SELECT tm.groupID
,tm.objectType
, tm.memberID
, 0 as [Nodelevel]
from #Hierarchy tm with(nolock) -- drop table #tblGrpsMatc
where groupID = 8
UNION ALL
SELECT
TC.groupID
,TC.objectType
, TC.memberID
, [Nodelevel] + 1 as [Nodelevel]
FROM CTE C2
inner join #Hierarchy TC with(nolock)
ON tc.groupID = C2.memberID
AND TC.groupID <> TC.memberID
-- AND ( TC.groupID <> C2.groupID AND C2.[Nodelevel] = 0)
)
select groupID, objectType, memberID, [Nodelevel] from CTE
July 25, 2016 at 1:18 pm
ah0996 (7/25/2016)
Hi,I need help with my CTE; All I need to find is the match between groupID and memberID and set nodelevel; once the result is already there, I want my CTE to stop. However, it is right now it is going in infinite loop because of 9,8.
I want my result to look like this:
groupIDobjectType memberIDNodelevel
8 group 1 0
1 group 9 1
1 user 7 1
9 group 8 2
---CODE---
Currently, results are going in infinite loop.
I have Hierarchy table:
drop table #Hierarchy
CREATE TABLE #Hierarchy( groupID int , memberID int, objectType varchar(max))
insert into #Hierarchy ( groupID , memberID , objectType ) values (8, 1,'group')
insert into #Hierarchy ( groupID , memberID , objectType ) values (1, 9,'group')
insert into #Hierarchy ( groupID , memberID , objectType ) values (1, 7,'user')
insert into #Hierarchy ( groupID , memberID , objectType ) values (9, 8,'group')
WITH CTE( groupID, objectType, memberID, [Nodelevel]) as
(SELECT tm.groupID
,tm.objectType
, tm.memberID
, 0 as [Nodelevel]
from #Hierarchy tm with(nolock) -- drop table #tblGrpsMatc
where groupID = 8
UNION ALL
SELECT
TC.groupID
,TC.objectType
, TC.memberID
, [Nodelevel] + 1 as [Nodelevel]
FROM CTE C2
inner join #Hierarchy TC with(nolock)
ON tc.groupID = C2.memberID
AND TC.groupID <> TC.memberID
-- AND ( TC.groupID <> C2.groupID AND C2.[Nodelevel] = 0)
)
select groupID, objectType, memberID, [Nodelevel] from CTE
Try this:
WITH CTE( groupID, objectType, memberID, [Nodelevel], OriginID) as
(
SELECT
tm.groupID
, tm.objectType
, tm.memberID
, 0 as [Nodelevel]
, tm.groupID
from #Hierarchy tm with(nolock) -- drop table #tblGrpsMatc
where groupID = 8
UNION ALL
SELECT
TC.groupID
,TC.objectType
, TC.memberID
, [Nodelevel] + 1 as [Nodelevel]
, C2.OriginID
FROM CTE C2
inner join #Hierarchy TC with(nolock)
ON tc.groupID = C2.memberID
AND TC.groupID <> TC.memberID
AND C2.OriginID <> TC.groupID
)
SELECT groupID, objectType, memberID, [Nodelevel]
FROM CTE;
July 25, 2016 at 2:53 pm
Thank you!!! It works:) what is this OriginID column in CTE? Please explain me.
Thank you soo much in advanced
July 26, 2016 at 6:19 am
It's actually a leftover from when I had to process the whole table at once. It's taking the GroupID value from the parent node and comparing it each child to prevent cyclic references. As you're using a single group, the query would have only needed an additional condition.
WITH CTE( groupID, objectType, memberID, [Nodelevel]) as
(
SELECT
tm.groupID
, tm.objectType
, tm.memberID
, 0 as [Nodelevel]
from #Hierarchy tm with(nolock) -- drop table #tblGrpsMatc
where groupID = 8
UNION ALL
SELECT
TC.groupID
,TC.objectType
, TC.memberID
, [Nodelevel] + 1 as [Nodelevel]
FROM CTE C2
inner join #Hierarchy TC with(nolock)
ON tc.groupID = C2.memberID
AND TC.groupID <> TC.memberID
WHERE TC.groupID <> 8
)
SELECT groupID, objectType, memberID, [Nodelevel]
FROM CTE;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply