Recursive Multi-Level Query using CTE. Attn: Experts!

I cannot give this a serious look until tomorrow; here are a couple of posts I have made previously concerning recursive queries and hierarchies:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1174303&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1219738&SiteID=1

I am puzzling over the way roleID is used in the example.  I can't see where the '2' comes from for your desired results.

I am afraid I don't understand how the RoleIDs work.

Also, I don't understand why OrgID 103 does not appear in the target output -- it is in the UserOrgRole table.

Also, please describe the order you wish for the output.  Do you wish the output ordered by only OrgID or by (1) hierarchy depth and then (2) Orgid

Not really clear what you're after, but this may help

with OrgRoles(OrgID,ParentID,RoleID) as
(select t.OrgID,
t.ParentID,
r.RoleID
from Org t
left outer join UserOrgRole r on r.OrgID=t.OrgID),
OrgRolesRec(OrgID,RoleID) as
(select OrgID,
RoleID
from OrgRoles
where ParentID is null
union all
select r.OrgID,
coalesce(r.RoleID,t.RoleID)
from OrgRolesRec t
inner join OrgRoles r on r.ParentID=t.OrgID)
select OrgID,
RoleID
from OrgRolesRec
--where RoleID=2
order by OrgID

Omaits:

I have assumed that you want the output ordered according to the hierarchy.  Also, I used data that was different than what you originally posted because I feel that the data provided does not test the routine sufficiently.  Actually, this data also might not test the routine sufficiently.  This is my first-pass guess:

-- --------------------------------------------------------
--   One problem that frequents lists of hierarchies is
--   that frequently it is desired to order the output
--   according to the hierarchy.  The problem with this is
--   that the order of an item is not only dependent on the
--   item itself but also its parent, its parent's parent,
--   etc.  This is not a simple ordering mechanism.
--
--   Therefore, this routine keeps string-list of the
--   hierarchy and sorts based on the hierarchy contained
--   in the string-list.  The hierarchy can be described as
--   a vector; the string-list is one way of abstracting
--   this kind of vector.
-- --------------------------------------------------------
declare @Org table
   (    OrgID       integer,
        ParentId    integer
   )
insert into @org values (200, null)
insert into @org values (111, 200)
insert into @org values (112, 200)
insert into @org values (105, 111)
insert into @org values (106, 111)
insert into @org values (107, 105)
insert into @org values (120, 105)
insert into @org values (131, 106)
insert into @org values (122, 106)
insert into @org values (121, 106)
select * from @org

--   OrgID       ParentId
--   ----------- -----------
--   200         NULL
--   111         200
--   112         200
--   105         111
--   106         111
--   107         105
--   120         105
--   131         106
--   122         106
--   121         106

declare @UserOrgRole table
   (    UserID      varchar(10),
        OrgID       integer,
        RoleID      integer
   )
insert into @UserOrgRole values ('User1', 200, 1)
insert into @UserOrgRole values ('User1', 111, 2)
insert into @UserOrgRole values ('User1', 112, 1)
insert into @UserOrgRole values ('User1', 105, 1)
insert into @UserOrgRole values ('User1', 106, 1)
insert into @UserOrgRole values ('User1', 107, 1)
insert into @UserOrgRole values ('User1', 120, 1)
insert into @UserOrgRole values ('User1', 131, 1)
insert into @UserOrgRole values ('User1', 122, 1)
insert into @UserOrgRole values ('User1', 121, 1)

--select * from @UserOrgRole

declare @prm_targetUser varchar(10)
declare @prm_targetRole integer

set @prm_targetUser = 'User1'
set @prm_targetRole = 2

;with userOrgRoleCTE
as
( select cast(1 as integer) as depth,
         a.OrgID,
         case when RoleId <> @prm_targetRole then 0
              else roleId
         end as RoleId,
         cast(convert(char(11), a.orgId) as varchar(111))
            as hierarchy
   from @UserOrgRole a
  inner join @org b
     on a.OrgId = b.orgId
    and b.parentId is null
    and UserID = @prm_targetUser
  union all
  select depth + 1 as depth,
         c.orgId,
         case when a.RoleId = @prm_targetRole then a.roleId
              when a.roleId <> @prm_targetRole
               and c.roleId = @prm_targetRole then c.roleId
              else 0
         end as RoleId,
         cast(a.hierarchy + convert(char(11), c.orgId) as varchar(111))
             as hierarchy
    from userOrgRoleCte a
   inner join @org b
      on a.orgId = b.parentId
   inner join @userOrgRole c
      on b.orgId = c.orgId
     and c.UserId = @prm_targetUser
)
select * from userOrgRoleCTE
--where roleId <> 0
order by hierarchy
--order by OrgId

-- --------------------------------------------
--   To remove the rows in which "RoleID" is
--   zero, simply uncomment the WHERE clause
-- --------------------------------------------

--   depth  OrgID  RoleId hierarchy
--   ------ ------ ------ ---------------------
--   1      200    0      200       
--   2      111    2      200   111       
--   3      105    2      200   111   105     
--   4      107    2      200   111   105   107
--   4      120    2      200   111   105   120
--   3      106    2      200   111   106     
--   4      121    2      200   111   106   121
--   4      122    2      200   111   106   122
--   4      131    2      200   111   106   131
--   2      112    0      200   112   

-- --------------------------------------------
--  If you simply order by "OrgID" you receive
--  the output in this order; this is probably
--  not what is wanted.
-- --------------------------------------------

--   depth  OrgID  RoleId hierarchy
--   ------ ------ ------ ---------------------
--   3      105    2      200   111   105     
--   3      106    2      200   111   106     
--   4      107    2      200   111   105   107
--   2      111    2      200   111  
--   2      112    0      200   112  
--   4      120    2      200   111   105   120
--   4      121    2      200   111   106   121
--   4      122    2      200   111   106   122
--   4      131    2      200   111   106   131
--   1      200    0      200       

A couple of things: first, if the desired sort order IS by OrgID the "hierarchy" column can be removed from the CTE -- for sorting by OrgID it brings unwanted overhead.

Also, I just realized that I did not build my vectors correctly.  The individual strings need to be right-justified instead of left-justified so that they order correctly.  I will shortly post a modified version of this query that fixes this problem.

Yes, I think this is a minor mod; I will include this modification when I switch from a left-justified to a right-justified vector.  I think a LEFT JOIN and use of the ISNULL function will fix this.  Give me a few minutes.

Kent

Here is what I have come up with. This bothers me because it is now more complex than I envisioned and this concerns me because simple code is often better than complicated code. Hopefully, somebody else -- maybe Mark -- can come up with a better way of doing this. It seems to work:

-- --------------------------------------------------------
-- One problem that frequents lists of hierarchies is
-- that frequently it is desired to order the output
-- according to the hierarchy. The problem with this is
-- that the order of an item is not only dependent on the
-- item itself but also its parent, its parent's parent,
-- etc. This is not a simple ordering mechanism.
--
-- Therefore, this routine keeps string-list of the
-- hierarchy and sorts based on the hierarchy contained
-- in the string-list. The hierarchy can be described as
-- a vector; the string-list is one way of abstracting
-- this kind of vector.
-- --------------------------------------------------------
declare @Org table
( OrgID integer,
ParentId integer
)
insert into @org values (200, null)
insert into @org values (111, 200)
insert into @org values (112, 200)
insert into @org values (105, 111)
insert into @org values (106, 111)
insert into @org values (107, 105)
insert into @org values (120, 105)
insert into @org values (131, 106)
insert into @org values (122, 106)
insert into @org values (121, 106)
--select * from @org

-- OrgID ParentId
-- ----------- -----------
-- 200 NULL
-- 111 200
-- 112 200
-- 105 111
-- 106 111
-- 107 105
-- 120 105
-- 131 106
-- 122 106
-- 121 106

declare @UserOrgRole table
( UserID varchar(10),
OrgID integer,
RoleID integer
)
/*
insert into @UserOrgRole values ('User1', 200, 1)
insert into @UserOrgRole values ('User1', 111, 2)
insert into @UserOrgRole values ('User1', 112, 1)
insert into @UserOrgRole values ('User1', 105, 1)
insert into @UserOrgRole values ('User1', 106, 1)
insert into @UserOrgRole values ('User1', 107, 1)
insert into @UserOrgRole values ('User1', 120, 1)
insert into @UserOrgRole values ('User1', 131, 1)
insert into @UserOrgRole values ('User1', 122, 1)
insert into @UserOrgRole values ('User1', 121, 1)
*/

insert into @UserOrgRole values ('User1', 111, 2)
--select * from @UserOrgRole

declare @prm_targetUser varchar(10)
declare @prm_targetRole integer

set @prm_targetUser = 'User1'
set @prm_targetRole = 2

-- --------------------------------------------------------
-- This version is modified to build the hierarchy
-- vectors right-justified instead of left-justified.
-- --------------------------------------------------------
;with userOrgRoleCTE
as
( select cast(1 as integer) as depth,
a.OrgID,
case when RoleId <> @prm_targetRole then 0
else roleId
end as RoleId,
cast(right(' ' + convert(varchar(11),
a.orgId), 11) as varchar(111))
as hierarchy
from @UserOrgRole a
inner join @org b
on a.OrgId = b.orgId
and b.parentId is null
and UserID = @prm_targetUser
union all
select cast(1 as integer) as depth,
a.OrgID,
0 as RoleId,
cast(right(' ' + convert(varchar(11),
a.orgId), 11) as varchar(111))
as hierarchy
from @org a
where a.parentId is null
and not exists
( select 0 from @UserOrgRole b
where a.orgId = b.orgId
and a.parentId is null
and UserId = @prm_targetUser
)
union all
select depth + 1 as depth,
b.orgId,
case when a.RoleId = @prm_targetRole then a.roleId
when a.roleId <> @prm_targetRole
and c.roleId = @prm_targetRole then c.roleId
else 0
end as RoleId,
cast(a.hierarchy + right(' '
+ convert(varchar(11), b.orgId), 11) as varchar(111))
as hierarchy
from userOrgRoleCte a
inner join @org b
on a.orgId = b.parentId
inner join @userOrgRole c
on b.orgId = c.orgId
and c.UserId = @prm_targetUser
union all
select depth + 1 as depth,
b.orgId,
case when a.RoleId = @prm_targetRole then a.roleId
else 0
end as RoleId,
cast(a.hierarchy + right(' '
+ convert(varchar(11), b.orgId), 11) as varchar(111))
as hierarchy
from userOrgRoleCte a
inner join @org b
on a.orgId = b.parentId
and not exists
( select 0 from @userOrgRole d
where b.orgId = d.orgId
and d.UserId = @prm_targetUser
)
)
select * from userOrgRoleCTE
--where roleId <> 0
order by hierarchy

-- --------------------------------------------
-- To remove the rows in which "RoleID" is
-- zero, simply uncomment the WHERE clause
-- --------------------------------------------

-- depth OrgID RoleId hierarchy
-- ------ ------ ------ ------------------------
-- 1 200 0 200
-- 2 111 2 200 111
-- 3 105 2 200 111 105
-- 4 107 2 200 111 105 107
-- 4 120 2 200 111 105 120
-- 3 106 2 200 111 106
-- 4 121 2 200 111 106 121
-- 4 122 2 200 111 106 122
-- 4 131 2 200 111 106 131
-- 2 112 0 200 112

Actually, you have given a very good gift -- you have offered to also give. And there lies my request; please pass the blessing that you have received in turn to someone else. When you have an opportunity to help someone else, please do so. I am also trying to pass it on. You are welcome!

Kent

Hi Kent,

I have a similar issue like the post above.

Hopefully you are willing to translate this code to my situation.
This is what I would like to achieve:

I have a view which gives me the the first three columns. Group by on ReceptuurID (only 1 shown in thius example)

Level and Sorting are the columns which I want to add in another view.

Thanks in advance for your help.

Frans

I am not sure I understand the sort of this list, so I will need help understanding the sort. Here is my first pass: