i've been staring @ code way long, trying figure out why final query returns unexpected results.
any appreciated. in advance.
given following code (running on sql server 2008 r2):
use tempdb; declare @emp--loyee table ( employeeid int not null ,employeename nvarchar(50) not null primary key(employeeid) ) insert @emp select 1,'fred' union select 2,'mary' union select 3,'joe' union select 4,'bill' declare @grp table ( groupid int not null ,groupname nvarchar(50) primary key(groupid) ) insert @grp select 1,'group 1' union select 2,'group 2' union select 3,'group 3' declare @empgrp table ( employeeid int not null ,groupid int not null primary key (employeeid,groupid) ) insert @empgrp select 1,1 union select 2,1 union select 3,1 union select 4,2 declare @grpgrp table ( groupid int not null ,parentgroupid int ,unique clustered(groupid,parentgroupid) ) insert @grpgrp select 1,2 union select 2,3; allempgroups (employeeid,groupid,rootgroupid) ( select cast(null int) employeeid,pgrp.groupid,pgrp.parentgroupid @grpgrp pgrp left join @grpgrp ggrp on pgrp.parentgroupid = ggrp.groupid union select e.employeeid,eg.groupid,aeg.rootgroupid @emp e join @empgrp eg on e.employeeid = eg.employeeid join @grpgrp ggrp on eg.groupid = ggrp.groupid join allempgroups aeg on aeg.groupid = ggrp.parentgroupid ) select employeeid,groupid,rootgroupid allempgroups
what is:
+------------+---------+-------------+ | employeeid | groupid | rootgroupid | +------------+---------+-------------+ | null | 1 | 2 | | null | 2 | 3 | | 1 | 1 | 3 | | 2 | 1 | 3 | | 3 | 1 | 3 | +------------+---------+-------------+
what expect/want this:
+------------+---------+-------------+ | employeeid | groupid | rootgroupid | +------------+---------+-------------+ | null | 1 | 2 | | null | 2 | 3 | | 4 | 2 | 3 | | 1 | 1 | 3 | | 2 | 1 | 3 | | 3 | 1 | 3 | +------------+---------+-------------+
bottom line, want full recursive stack of employees beneath given root group(s), root group id on every row.
what missing?
first:
- you need row root node in
@grpgrpvalues3, null - the anchor (part before
union all) of recursive cte needs root node (3, null) ancestor first recursion.
... insert @grpgrp select 1,2 union select 2,3 union select 3, null; allempgroups (employeeid,groupid,rootgroupid) ( select cast(null int) employeeid,pgrp.groupid, parentgroupid = pgrp.groupid @grpgrp pgrp left join @grpgrp ggrp on pgrp.parentgroupid = ggrp.groupid pgrp.parentgroupid null union select e.employeeid,eg.groupid,aeg.rootgroupid @emp e join @empgrp eg on e.employeeid = eg.employeeid join @grpgrp ggrp on eg.groupid = ggrp.groupid join allempgroups aeg on aeg.groupid = ggrp.parentgroupid ) select employeeid,groupid,rootgroupid allempgroups rextester demo: http://rextester.com/cbwy80387
returns:
+------------+---------+-------------+ | employeeid | groupid | rootgroupid | +------------+---------+-------------+ | null | 3 | 3 | | 4 | 2 | 3 | | 1 | 1 | 3 | | 2 | 1 | 3 | | 3 | 1 | 3 | +------------+---------+-------------+ beyond that, build groups hierarchy first, then join employees so:
with allempgroups (groupid,parentgroupid,rootgroupid) ( select pgrp.groupid, pgrp.parentgroupid, rootgroupid = groupid @grpgrp pgrp pgrp.parentgroupid null union select ggrp.groupid,ggrp.parentgroupid,aeg.rootgroupid @grpgrp ggrp inner join allempgroups aeg on aeg.groupid = ggrp.parentgroupid ) select eg.employeeid,aeg.* allempgroups aeg left join @empgrp eg on eg.groupid = aeg.groupid rextester demo: http://rextester.com/fak76354
returns:
+------------+---------+---------------+-------------+ | employeeid | groupid | parentgroupid | rootgroupid | +------------+---------+---------------+-------------+ | null | 3 | null | 3 | | 4 | 2 | 3 | 3 | | 1 | 1 | 2 | 3 | | 2 | 1 | 2 | 3 | | 3 | 1 | 2 | 3 | +------------+---------+---------------+-------------+ 
Comments
Post a Comment