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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy